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

Submitted by admin on Wed, 2004-08-04 15:13
Body: 

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 math, try this method (contributed by ):

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

»

Comments

Submitted by Shilpa Petrim (not verified) on Wed, 2006-05-03 18:09.

NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are 'DAY', 'HOUR', 'MINUTE' and 'SECOND'.

This function converts the number x into an INTERVAL DAY TO SECOND datatype.

SQL> select numtodsinterval(date1-date2,'day') time_difference from dates;

TIME_DIFFERENCE
----------------------------------------------------------------
+000000001 00:00:00.000000000
+000000000 01:00:00.000000000
+000000000 00:01:00.000000000

»

Submitted by James Peterson (not verified) on Thu, 2006-05-04 13:54.

Thanks you very much Shilpa Petrim. An awesome solution. Good job in suggesting an efficient solution of the latest Oracle 9i.

»

Submitted by Hasan Amer (not verified) on Tue, 2006-06-06 01:56. 

SQL> SELECT floor((date1-date2)*24)
  2         || ' HOURS ' ||
  3         mod(floor((date1-date2)*24*60),60)
  5         || ' MINUTES ' ||
  6         mod(floor((date1-date2)*24*60*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

»

Submitted by favour.sri@gmail.com on Tue, 2013-05-28 01:33.

Sometimes I'm getting negative values when I use the above query.

Output:

EXCUTION_TIME
0:00:09
-1:59:53
0:00:31
0:00:09
0:00:15
-1:59:46

»

Submitted by saha.sandipan on Tue, 2009-12-22 06:35. 

CREATE OR REPLACE FUNCTION datediff
(
time1 TIMESTAMP
, time2 TIMESTAMP
)
-- RETURN NUMBER
RETURN number  
AS sec NUMBER;

BEGIN

SELECT (extract(DAY FROM time2-time1)*24*60*60)+ 
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)
into sec FROM dual;

RETURN sec;

END;

»