Oracle: Calculate time difference in HH:MM:SS between 2 dates

Question

I have 2 dates with following format:

ST_DT = Sun Dec 29 11:55:29 EST 2013

ED_DT = Tue Dec 30 20:21:34 EST 2013

I want to find the difference between these 2 dates in HH:MM:SS format. Now my problem is that i don't know how to parse the above date format in Oracle.

Have you seen [this](http://stackoverflow.com/questions/11500098/get-the-difference-between-two-dates-both-in-months-and-days-in-sql) ?? — Anubhab, Jan 01 '14 at 09:29
Dates don't have a "format". The format is only applied when the values are displayed. — , Jan 01 '14 at 09:44
1) There is going to be a conflict( `ORA-01835` error will be raised) between name of the day of a week `Tue` and day of month `30` (Tuesday is the 31st day of December 2013, not 30th ) when you try to convert the second date `ED_DT` to a value of date data type; 2) What output in terms of `hh:mi:ss` format will you expect if the difference between two dates exceeds 12(24) hours? — Nick Krasnov, Jan 01 '14 at 09:53
ED_DT = Mon Dec 30 20:21:34 EST 2013 Can you share how to convert to date data type for such dates — Prabhat, Jan 01 '14 at 09:59

score 1 · Answer 1 · answered Jan 01 '14 at 12:51

Are the dates in varchar2 type? Then, you can first convert it into timestamp format. Since it has timezone also, use the to_timestamp_tz function.

SQL> select to_timestamp_tz('Sun Dec 29 11:55:29 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy') from dual;

TO_TIMESTAMP_TZ('SUNDEC2911:55:29EST2013','DYMONDDHH24:MI:SSTZRYYYY')
---------------------------------------------------------------------------
29-DEC-13 11.55.29.000000000 AM EST

Once the dates are in timestamp type, subtracting them will give you the difference in interval day to second type.

SQL> select   to_timestamp_tz ('Mon Dec 30 20:21:34 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy')
  2         - to_timestamp_tz ('Sun Dec 29 11:55:29 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy') from dual;

TO_TIMESTAMP_TZ('MONDEC3020:21:34EST2013','DYMONDDHH24:MI:SSTZRYYYY')-TO_TI
---------------------------------------------------------------------------
+000000001 08:26:05.000000000

Then use extract to get the individual components from the interval.

SQL> select extract(day from intrvl) as dd,
  2         extract(hour from intrvl) as hh24,
  3         extract(minute from intrvl) as mi,
  4         extract(second from intrvl) as ss
  5  from (
  6        select   to_timestamp_tz ('Mon Dec 30 20:21:34 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy')
  7               - to_timestamp_tz ('Sun Dec 29 11:55:29 EST 2013','Dy Mon dd hh24:mi:ss TZR yyyy') as intrvl
  8       from dual
  9       );

        DD       HH24         MI         SS
---------- ---------- ---------- ----------
         1          8         26          5

score 0 · Answer 2 · answered Jan 01 '14 at 10:44

I figured out one solution but it will work only for same time zone dates.

with tab as (
select to_date(replace(substr('Sun Dec 28 23:59:59 EST 2013', 4), 'EST '), 'Mon DD HH24:MI:SS RRRR') start_date,
       to_date(replace(substr('Tue Dec 30 20:21:34 EST 2013', 4), 'EST '), 'Mon DD HH24:MI:SS RRRR') end_date
  from dual),
tab_sec as
(select ((end_date - start_date) * 24 * 60 * 60) sec from tab)
select lpad(trunc(sec / (60*60)), 2, '0')||':'|| 
       lpad(trunc((sec - (trunc(sec / (60*60)) * 60 * 60))/60), 2, '0')||':'||
       lpad(mod(sec, 60), 2, '0') diff 
 from tab_sec;

score 0 · Answer 3 · answered Jan 01 '14 at 10:54

You can use NUMTODSINTERVAL

For example

with x as (
   select  to_date('01/01/2014 10:00:00','dd/mm/yyyy hh24:mi:ss') d1 ,
           to_date('01/01/2014 12:00:00','dd/mm/yyyy hh24:mi:ss') d2
   from dual
   union all
   select  to_date('02/01/2014 10:00:00','dd/mm/yyyy hh24:mi:ss') d1 ,
           to_date('01/01/2014 12:00:00','dd/mm/yyyy hh24:mi:ss') d2
   from dual
   union all
   select  to_date('01/01/2014 10:30:00','dd/mm/yyyy hh24:mi:ss') d1 ,
           to_date('01/01/2014 12:00:00','dd/mm/yyyy hh24:mi:ss') d2
   from dual
   union all
   select  to_date('01/01/2014 10:00:30','dd/mm/yyyy hh24:mi:ss') d1 ,
           to_date('01/01/2014 12:00:00','dd/mm/yyyy hh24:mi:ss') d2
   from dual
   union all
   select  to_date('01/01/2014 10:00:30','dd/mm/yyyy hh24:mi:ss') d1 ,
           to_date('02/01/2014 12:20:10','dd/mm/yyyy hh24:mi:ss') d2
   from dual
)
select d1 , d2 , numtodsinterval(d2 - d1, 'day') as interval_diff
from x

D1                  D2                  INTERVAL_DIFF
------------------- ------------------- ---------------------------------
01/01/2014 10:00:00 01/01/2014 12:00:00 +000000000 02:00:00.000000000
02/01/2014 10:00:00 01/01/2014 12:00:00 -000000000 22:00:00.000000000
01/01/2014 10:30:00 01/01/2014 12:00:00 +000000000 01:30:00.000000000
01/01/2014 10:00:30 01/01/2014 12:00:00 +000000000 01:59:30.000000000
01/01/2014 10:00:30 02/01/2014 12:20:10 +000000001 02:19:39.999999999

Oracle: Calculate time difference in HH:MM:SS between 2 dates

3 Answers3

Linked

Related