Oracle data output
4 answers
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.
+4
source to share
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
+6
source to share
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
)
)
+3
source to share