Oracle data output
How do I write an expression that gives me the difference between two dates in days, hours, minutes, seconds, etc.? By default, subtracting two dates in oracle returns days as decmial.
This decimal number is the difference in days between two dates. You can do a little math to convert it to days, hours, minutes, seconds, etc.
EDIT . I see what you are looking for. I'm sure there is an easier way, but I would probably accomplish it like this:
select trunc(5.3574585) days,
trunc(mod((5.3574585) * 24, 24)) hours,
trunc(mod((5.3574585) * 24 * 60, 60)) minutes,
trunc(mod((5.3574585) * 24 * 60 * 60, 60)) seconds
from dual;
... where 5.3574585 is the number of days returned by subtraction ...
Note: this is not actually tested, it is from the head.
Using:
SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
TRUNC(86400*(date2-date1)) - 60*(TRUNC((86400*(date2-date1))/60)) seconds,
TRUNC((86400*(date2-date1))/60) - 60*(TRUNC(((86400*(date2-date1))/60)/60)) minutes,
TRUNC(((86400*(date2-date1))/60)/60) - 24*(TRUNC((((86400*(date2-date1))/60)/60)/24)) hours,
TRUNC((((86400*(date2-date1))/60)/60)/24) days,
TRUNC(((((86400*(date2-date1))/60)/60)/24)/7) weeks
FROM TABLE
Ref: Comparison of Oracle DATE and TIMESTAMP Data Types
You can convert dates to timestamps and use your own functions to output individual components ...
SELECT EXTRACT( DAY FROM ( end_timestamp - start_timestamp ) ) days
, EXTRACT( HOUR FROM ( end_timestamp - start_timestamp ) ) hours
, EXTRACT( MINUTE FROM ( end_timestamp - start_timestamp ) ) minutes
, EXTRACT( SECOND FROM ( end_timestamp - start_timestamp ) ) seconds
FROM ( SELECT TO_TIMESTAMP( TO_CHAR( start_date, 'DD/MM/YYYY HH24:MI:SS' )
, 'DD/MM/YYYY HH24:MI:SS' ) start_timestamp
, TO_TIMESTAMP( TO_CHAR( end_date, 'DD/MM/YYYY HH24:MI:SS' )
, 'DD/MM/YYYY HH24:MI:SS' ) end_timestamp
FROM ( SELECT TO_DATE( '01/10/2009 14:25:01'
, 'DD/MM/YYYY HH24:MI:SS' ) start_date
, TO_DATE( '03/10/2009 23:09:15'
, 'DD/MM/YYYY HH24:MI:SS' ) end_date
FROM dual
)
)
Why not just convert to a timestamp and implicitly use the interval from the second to the second data type?
select to_timestamp(sysdate+1.1234) - to_timestamp(sysdate) diff
from dual
/
DIFF
-----------
1 2:57:42.0