How does one get the time difference between two date columns?

Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.

Let's investigate some solutions. Test data:

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);
Table created.
SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
1 row created.
SQL> SELECT (date1 - date2) FROM dates;
DATE1-DATE2
-----------
          1
 .041666667
 .000694444

Solution 1

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
  2         || ' HOURS ' ||
  3         floor((((date1-date2)*24*60*60) -
  4         floor(((date1-date2)*24*60*60)/3600)*3600)/60)
  5         || ' MINUTES ' ||
  6         round((((date1-date2)*24*60*60) -
  7         floor(((date1-date2)*24*60*60)/3600)*3600 -
  8         (floor((((date1-date2)*24*60*60) -
  9         floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
 10         || ' SECS ' time_difference
 11    FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS

Solution 2

If you don't want to go through the floor and ceiling maths, try this method:

SQL> SELECT to_number( to_char(to_date('1','J') +
  2         (date1 - date2), 'J') - 1)  days,
  3         to_char(to_date('00:00:00','HH24:MI:SS') +
  4         (date1 - date2), 'HH24:MI:SS') time
  5   FROM dates;
      DAYS TIME
---------- --------
         1 00:00:00
         0 01:00:00
         0 00:01:00

Solution 3

Here is a simpler method:

SQL> SELECT trunc(date1-date2) days,
  2     to_char(trunc(sysdate) + (date1 - date2), 'HH24 "Hours" MI "Minutes" SS "Seconds"') time
  3   FROM dates;
      DAYS TIME
---------- ------------------------------
         1 00 Hours 00 Minutes 00 Seconds
         0 01 Hours 00 Minutes 00 Seconds
         0 00 Hours 01 Minutes 00 Seconds