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:
CREATE TABLE dates (date1 DATE, date2 DATE); INSERT INTO dates VALUES (SYSDATE, SYSDATE-1); INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24); INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24); SELECT (date1 - date2) FROM dates; DATE1-DATE2 ----------- 1 .041666667 .000694444
Solution 1
SELECT floor(((date1-date2)*24*60*60)/3600) || ' HOURS ' || floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600 - (floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference FROM dates; TIME_DIFFERENCE -------------------------------------------------------------------------------- 24 HOURS 0 MINUTES 0 SECS 1 HOURS 0 MINUTES 0 SECS 0 HOURS 1 MINUTES 0 SECS
An enhancement to solution 1
SELECT floor((date1-date2)*24) || ' HOURS ' || mod(floor((date1-date2)*24*60),60) || ' MINUTES ' || mod(floor((date1-date2)*24*60*60),60) || ' SECS ' time_difference FROM dates;</code> 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 Erik Wile):
SELECT to_number( to_char(to_date('1','J') + (date1 - date2), 'J') - 1) days, to_char(to_date('00:00:00','HH24:MI:SS') + (date1 - date2), 'HH24:MI:SS') time FROM dates; DAYS TIME ---------- -------- 1 00:00:00 0 01:00:00 0 00:01:00
Solution 3: If u want an easier method, use numtodsinterval()
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.
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
Recent Comments