Oracle display more than 24 hours

问题:

I have a table with two DATE columns END_TIME and START_TIME. On this table I run the following query:

SELECT y.ID,
       TO_CHAR(  TO_DATE('00:00:00', 'HH24:MI:SS') + (y.END_TIME - y.START_TIME) 
               , 'HH24:MI:SS') AS RUNTIME,
       y.END_TIME - y.START_TIME AS RUNTIME2,
       TO_CHAR(y.START_TIME, 'DD-MON-YYYY HH24:MI:SS') AS START_TIME,
       TO_CHAR(y.END_TIME, 'DD-MON-YYYY HH24:MI:SS') AS END_TIME
FROM mytable y;

I get these two rows as a result:

ID | RUNTIME | RUNTIME2                                   | START_TIME          | END_TIME
------------------------------------------------------------------------------------------------------
 1 | 04:26:17| 0.1849189814814814814814814814814814814815 | 30-JAN-2015 19:45:48| 31-JAN-2015 00:12:05
 2 | 03:28:18| 1.14465277777777777777777777777777777778   | 06-FEB-2015 20:47:22| 08-FEB-2015 00:15:40

As you can see, ID 2 had a runtime larger than 24 hours. How can I change my query so that RUNTIME for ID 2 will display 27:28:18 instead?

回答1:

You need to pull the time difference apart into its constituent day, hour, minute and second elements, combine the number of days * 24 with the number of hours, and stick it back together.

When subtracting dates you get the difference as the number of days, so you need to convert the fractional part into the other elements, which you can do with a combination of trunc and mod; using a CTE to make this slightly easier to follow and showing each valu eon its own as well as combined into a single string:

with y as (
  select id, end_time - start_time as runtime
  from mytable
)
select id,
  runtime,
  trunc(runtime) as days,
  24 * trunc(runtime) as day_hours,
  trunc(24 * mod(runtime, 1)) as hours,
  trunc(60 * mod(24 * (runtime), 1)) as minutes,
  trunc(60 * mod(24 * 60 * (runtime), 1)) as seconds,
  lpad(24 * trunc(runtime)
    + trunc(24 * mod(runtime, 1)), 2, '0')
    ||':'|| lpad(trunc(60 * mod(24 * (runtime), 1)), 2, '0')
    ||':'|| lpad(trunc(60 * mod(24 * 60 * (runtime), 1)), 2, '0')
    as runtime
from y;

        ID    RUNTIME       DAYS  DAY_HOURS      HOURS    MINUTES    SECONDS RUNTIME 
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
         1 .184918981          0          0          4         26         16 04:26:16 
         2 1.14465278          1         24          3         28         18 27:28:18 

You could also convert the dates to timestamps for the calculation, which gives you an interval type, and then use the extract function to get the elements instead; the principal is the same though:

with y as (
  select id,
    cast(end_time as timestamp) - cast (start_time as timestamp) as runtime
  from mytable
)
select id,
  runtime,
  extract (day from runtime) as days,
  24 * extract (day from runtime) as day_hours,
  extract (hour from runtime) as hours,
  extract (minute from runtime) as minutes,
  extract (second from runtime) as seconds,
  lpad(24 * extract (day from runtime) + extract (hour from runtime), 2, '0')
    ||':'|| lpad(extract (minute from runtime), 2, '0')
    ||':'|| lpad(extract (second from runtime), 2, '0')
    as runtime
from y;

        ID RUNTIME           DAYS  DAY_HOURS      HOURS    MINUTES    SECONDS RUNTIME 
---------- ----------- ---------- ---------- ---------- ---------- ---------- --------
         1 0 4:26:17.0          0          0          4         26         17 04:26:17 
         2 1 3:28:18.0          1         24          3         28         18 27:28:18 

Or a slight variation, getting the difference from the dates and then converting that to an interval:

with y as (
  select id,
    numtodsinterval(end_time - start_time, 'DAY') as runtime
  from mytable
)
...

SQL Fiddle demo.