5

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.

user2588995
  • 51
  • 1
  • 2
  • Have a look: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns – PM 77-1 Jul 16 '13 at 21:26

3 Answers3

3

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
  )
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • I implemented your code like this but it says ORA-00936: missing expression. Select WITH spans AS ( SELECT end_date - start_date AS Elapsed FROM my_date_table ) SELECT EXTRACT(DAY FROM Elapsed) * 24 + EXTRACT(HOUR FROM Elapsed) || ':' || EXTRACT(MINUTE FROM Elapsed) || ':' || ROUND(EXTRACT(SECOND FROM Elapsed), 0) AS duration FROM spans from my_table; – user2588995 Jul 17 '13 at 08:15
  • @user2588995 - Ed posted the entire query; you don't need the `select ... from my_table` that you've wrapped around it. The `WITH` clause selects the difference from your table; the `SELECT` then uses that to format the results, effectively. You can think of the `WITH` as a temporary view. – Alex Poole Jul 17 '13 at 12:06
  • Thank you for reply. I need to put it in a view. How do i do this? It says missing esxpression. I read that i cant put with clause in a view ? My view: create or replace view order as select customer_id,name,start_date,end_date from cust_order – user2588995 Jul 19 '13 at 11:38
  • I think you're right that the `WITH` doesn't work with views. You can get the same result by moving the `WITH` piece into a subquery. I used `WITH` purely by reflex; the subquery will work just as well. Please see the addendum to my answer above, which uses a subquery. – Ed Gibbs Jul 19 '13 at 13:11
  • Thank you for reply Ed Gibbs. If i start the new code it says: ORA-30076 invalid extract field for extract source. I guess the query :select end_date-open_date as Elapsed displays a decimal value so it cant extract hour, minute and second? I hope you can help me. Have a nice day – user2588995 Jul 22 '13 at 08:33
  • Sorry about that! I left out the `NumToDSInterval` function around the end and start dates - that's what turns the decimal value into an `INTERVAL DAY TO SECOND` value so you can `EXTRACT` the various time components. Please see the last query in my updated answer; that should be a lot closer to what you need. Note that the `NumToDSInterval` function takes two arguments: the first is your date math (`end - start`) and the second is the constant `'DAY'`. – Ed Gibbs Jul 22 '13 at 14:53
  • Thanks for the hint concerning NumToDSInterval. In my case the elapsed time is always positive, so I reformatted the output with a regex to look a little nicer like so: Regexp_Replace(NumToDSInterval(a.end - a.start, 'DAY'), '\+0*|\..*', '') – Oliver Sep 17 '20 at 11:35
1

I notice that you want hours exceeding one day. The following gives you days, hours, minutes, seconds, and works up to 31 days. The idea is to convert this to a date and then use to_char() for the conversion:

select to_char(cast('01-Jan-2000' as date) + duration,
               'dd hh:mi:ss')

Not ideal, but quick.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think you need to use a built in oracle function. Try the Date SUB as seen here Oracle Docs / MySQL

This will allow you to set the precision on the time that is returned.

spartikus
  • 2,852
  • 4
  • 33
  • 38
  • 1
    I think you probably meant [timediff](http://docs.oracle.com/cd/E17952_01/refman-5.1-en/date-and-time-functions.html#function_timediff), but these are still MySQL functions and don't work with Oracle RDBMS. – Alex Poole Jul 16 '13 at 22:16