# 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 `DATE`s 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
);
`````` PDF - Download Oracle Database for free