Time difference in oracle

Question

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?

Maybe this post can help http://stackoverflow.com/questions/970249/format-interval-with-to-char as if `endtime` and `starttime` are of type DATE then `endtime-starttime` will be an interval. BTW why do you want to hold a column with data that can be calculated in the query? not better use [virtual columns](http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3001.htm#BABIDDJJ) — A.B.Cade, Dec 05 '13 at 06:15
@A.B.Cade i have tried like this no use `select TO_CHAR(NVL(endtime-starttime, TO_DSINTERVAL('0 00:00:00'))) from purge_archive_status_log ` — Sathish, Dec 05 '13 at 06:22
No, according to the post you need to do something like this: `to_char(extract(HOUR FROM (endtime-starttime)), 'fm00') || ':' || to_char(extract(MINUTE FROM (endtime-starttime)), 'fm00') || ':' || to_char(extract(SECOND FROM (endtime-starttime)), 'fm00')` — A.B.Cade, Dec 05 '13 at 06:32

Nick Krasnov · Accepted Answer · 2013-12-05T06:59:22.597

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

score 2 · Answer 2 · answered Dec 05 '13 at 06:32

Try this too,

WITH TIME AS (
SELECT to_date('02-12-2013 01:24:00', 'dd-mm-yyyy hh24:mi:ss') starttime, 
       to_date('02-12-2013 04:17:00', 'dd-mm-yyyy hh24:mi:ss') endTime
FROM dual)
SELECT to_char(TRUNC ((endTime - startTime)* 86400 / (60 * 60)), 'fm09')||':'||
       to_char(TRUNC (MOD ((endTime - startTime)* 86400, (60*60)) / 60), 'fm09')||':'||
       to_char(MOD((endTime - startTime)* 86400, 60), 'fm09') time_diff
FROM   TIME;

Time difference in oracle

2 Answers2