Oracle Database 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
);