Oracle Database Dates Date Arithmetic - Difference between Dates in Days, Hours, Minutes and/or Seconds
Example
In oracle, the difference (in days and/or fractions thereof) between two DATEs can be found using subtraction:
SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL;
Outputs the number of days between the two dates:
DIFFERENCE
----------
89
And:
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
Outputs the fraction of days between two dates:
DIFFERENCE
----------
1.0425
The difference in hours, minutes or seconds can be found by multiplying this number by 24, 24*60 or 24*60*60 respectively.
The previous example can be changed to get the days, hours, minutes and seconds between two dates using:
SELECT TRUNC( difference ) AS days,
TRUNC( MOD( difference * 24, 24 ) ) AS hours,
TRUNC( MOD( difference * 24*60, 60 ) ) AS minutes,
TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM (
SELECT TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
);
(Note: TRUNC() is used rather than FLOOR() to correctly handle negative differences.)
Outputs:
DAYS HOURS MINUTES SECONDS
---- ----- ------- -------
1 1 1 12
The previous example can also be solved by converting the numeric difference to an interval using NUMTODSINTERVAL():
SELECT EXTRACT( DAY FROM difference ) AS days,
EXTRACT( HOUR FROM difference ) AS hours,
EXTRACT( MINUTE FROM difference ) AS minutes,
EXTRACT( SECOND FROM difference ) AS seconds
FROM (
SELECT NUMTODSINTERVAL(
TO_DATE( '2016-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL
);
