-1

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?

diziaq
  • 6,881
  • 16
  • 54
  • 96
BaseBallBatBoy
  • 685
  • 2
  • 12
  • 25
  • What is the purpose of `TO_DATE('00:00:00','HH24:MI:SS') `? – Lalit Kumar B Feb 10 '15 at 13:04
  • if I don't use that one I get ORA-01481: invalid number format model 01481. 00000 - "invalid number format model" *Cause: The user is attempting to either convert a number to a string via TO_CHAR or a string to a number via TO_NUMBER and has supplied an invalid number format model parameter. *Action: Consult your manual. – BaseBallBatBoy Feb 10 '15 at 13:12
  • It would be better if you post the create and insert statements and your desired output. Explain the rules to derive your desired output. – Lalit Kumar B Feb 10 '15 at 13:17
  • Maybe useful: [oracle-convert-seconds-to-hoursminutesseconds](https://stackoverflow.com/questions/11003918/oracle-convert-seconds-to-hoursminutesseconds) – Ryan Vincent Feb 10 '15 at 14:03
  • Took me a while to find something that wasn't converting the other way, to milliseconds; but possible a duplicate of [this question](http://stackoverflow.com/q/3978656/266304)? Or [this one](http://stackoverflow.com/q/14030654/266304)? – Alex Poole Feb 10 '15 at 16:06

1 Answers1

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318