Time difference in oracle

oracle, timestamp

Solution

There is probably no reason to have that `total_time_taken` column in your table at all, you can always calculate it's value. But If you insist on keeping it, it would be better to recreated it as column of `interval day to second` data type, not `varchar2`(assuming that that's its current data type). So here are two queries for you to choose from, one returns value of `interval day to second` data type and another one value of `varchar2` data type:

This query returns difference between two dates as a value of `interval day to second` data type:

SQL> with t1(starttime, endtime, total_time_taken ) as(
  2    select to_date('02-12-2013 01:24:00', 'dd/mm/yyyy hh24:mi:ss')
  3         , to_date('02-12-2013 04:17:00', 'dd/mm/yyyy hh24:mi:ss')
  4         , '02:53:00'
  5     from dual
  6  )
  7  select starttime
  8       , endtime
  9       , (endtime - starttime) day(0) to second(0) as total_time_taken
 10   from t1
 11  ;

Result:

STARTTIME            ENDTIME               TOTAL_TIME_TAKEN  
-----------          -----------          ---------------- 
02-12-2013 01:24:00  02-12-2013 04:17:00   +0 02:53:00

This query returns difference between two dates as a value of `varchar2` data type:

SQL> with t1(starttime, endtime, total_time_taken ) as(
  2    select to_date('02-12-2013 01:24:00', 'dd/mm/yyyy hh24:mi:ss')
  3         , to_date('02-12-2013 04:17:00', 'dd/mm/yyyy hh24:mi:ss')
  4         , '02:53:00'
  5     from dual
  6  )
  7  select starttime
  8       , endtime
  9       , to_char(extract(hour   from res), 'fm00')  || ':' ||
 10         to_char(extract(minute from res), 'fm00')  || ':' ||
 11         to_char(extract(second from res), 'fm00') as total_time_taken
 12    from(select starttime
 13              , endtime
 14              , total_time_taken
 15              , (endtime - starttime) day(0) to second(0) as res
 16          from t1
 17        )
 18  ;

Result:

STARTTIME            ENDTIME              TOTAL_TIME_TAKEN  
-----------          -----------          ---------------- 
02-12-2013 01:24:00  02-12-2013 04:17:00   02:53:00

Problem

Hi i have the following table which contains Start time,end time, total time ``` STARTTIME | ENDTIME | TOTAL TIME TAKEN | 02-12-2013 01:24:00 | 02-12-2013 04:17:00 | 02:53:00 | ``` I need to update the `TOTAL TIME TAKEN` field as above using the update query in oracle For that I have tried the following select query ``` select round((endtime-starttime) * 60 * 24,2), endtime, starttime from purge_archive_status_log ``` but I'm getting 02.53 as a result, but my expectation format is 02:53:00 Please let me know how can I do this?

Original source

Related problems