Geek Answers Handbook

Oracle time difference

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 a field TOTAL TIME TAKENas above using update request in oracle

For this, I tried the following select query

select  round((endtime-starttime) * 60 * 24,2),
        endtime,
        starttime 
  from purge_archive_status_log

but I get 02.53 but my wait format is 02:53:00 Please let me know how can I do this?

+4
source share
2 answers

, total_time_taken , . , interval day to second, varchar2 ( , ). , , interval day to second varchar2:

interval day to second:

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  ;

:

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

varchar2:

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  ;

:

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

,

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;
+2

More articles:


All Articles