CONVERT decimal DAYS to TIME format

Question

Hello iam a newbie in sql and i need your help.

I have the following table:

    start_date      |     end_date          
02.08.2012 09:27:19 |  06.08.2012 07:53:00  
06.08.2012 06:58:58 |  06.08.2012 13:42:33
05.06.2012 14:35:58 |  05.06.2012 14:42:31

I need to display the duration between start_date and end_date.

I did it like this: end_date - start_date = duration
but it displays a decimal value (see below table - field duration).

I need field duration to display like this: HH:MM:SS - not decimal.

    start_date      |     end_date         |         duration        | I need it like this
02.08.2012 09:27:19 |  06.08.2012 07:53:00 | --> 3.93450231481481    |    94:25:41
06.08.2012 06:58:58 |  06.08.2012 13:42:33 | --> 0.280266203703704   |    06:43:35
05.06.2012 14:35:58 |  05.06.2012 14:42:31 | --> 0.0045486111111...  |    00:06:33

If something is uncertain i will try to explain. I hope you can help me. Have a nice day.

Answer 1

The subtraction of the dates gives you the number of days. You can turn them into an INTERVAL DAY TO SECOND value using the NumToDSInterval function.

With start date 02.08.2012 09:27:19 and end date 06.08.2012 07:53:00, the result is close but not quite what you want (and notice the float-type rounding thing where 41 seconds becomes 40.9999999...):

SQL> SELECT NumToDSInterval(
  2    TO_DATE('06.08.2012 07:53:00', 'DD.MM.YYYY HH24:MI:SS') -
  3    TO_DATE('02.08.2012 09:27:19', 'DD.MM.YYYY HH24:MI:SS'), 'DAY') AS Elapsed
  4  FROM DUAL;

ELAPSED
-----------------------------
+000000003 22:25:40.999999999

But it's a good starting point because once the elapsed time is in an INTERVAL type you can EXTRACT days, hours, minutes and seconds. I'd do it something like this:

WITH spans AS (
  SELECT NUMTODSINTERVAL(end_date - start_date, 'DAY') AS Elapsed
  FROM myTable
)
SELECT
  EXTRACT(DAY FROM Elapsed) * 24 + EXTRACT(HOUR FROM Elapsed) || ':' ||
  EXTRACT(MINUTE FROM Elapsed) || ':' ||
  ROUND(EXTRACT(SECOND FROM Elapsed), 0) AS duration
FROM spans

I tried this with your first set of dates and it worked just fine; the ROUND made the seconds come out correctly as 41.

---

Addendum OP needs to use this logic in a view, and I'm pretty sure a CTE (Common Table Expression, otherwise know as "WITH foo AS (query)") won't fly for a view.

To use this in a view, move the CTE to a subquery instead:

CREATE OR REPLACE VIEW myView AS
  SELECT
    EXTRACT(DAY FROM Elapsed) * 24 + EXTRACT(HOUR FROM Elapsed) || ':' ||
    EXTRACT(MINUTE FROM Elapsed) || ':' ||
    ROUND(EXTRACT(SECOND FROM Elapsed), 0) AS duration
  FROM (
    SELECT NUMTODSINTERVAL(end_date - start_date, 'DAY') AS Elapsed
    FROM myTable
  )