How to get the date difference between start date and end date in oracle as hours and minutes

0
votes

I have a scenario in which for example,my start_date ='12-SEP-2018 00:01:00' and End_date ='13-SEP-2018 14:55:00' . The difference between the 2 dates must be found out in Hours and minutes like'12:20'. This must be achieved in oracle database. I tried using the following logic :

SELECT 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') - to_date( '2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours FROM dual; I was able to get the hour difference but unable to get minutes along with it.

2
oracleoracle10goracle12c
Why do you want hours:minutes format? I can't think of a scenario where that would be more useful than just hours or minutes as a decimal. - Tim Biegeleisen
This is required to derive the total run time of a particular workflow in Informatica Repository , like a workflow ran for 12 hours and 30 minutes. - karthik

2 Answers

1
votes
CREATE TABLE table_name ( start_date DATE, end_date DATE );

INSERT INTO table_name VALUES ( TIMESTAMP '2009-07-07 19:30:00', TIMESTAMP '2009-07-07 22:00:00' );

Then you can subtract one from the other and cast it to a DAY TO SECOND interval and then just EXTRACT the component parts of the time:

SELECT EXTRACT( DAY    FROM difference ) AS days,
       EXTRACT( HOUR   FROM difference ) AS hours,
       EXTRACT( MINUTE FROM difference ) AS minutes,
       EXTRACT( SECOND FROM difference ) AS seconds
FROM   (
  SELECT ( end_date - start_date ) DAY TO SECOND AS difference
  FROM   table_name
);

Outputs:

DAYS | HOURS | MINUTES | SECONDS
---: | ----: | ------: | ------:
   0 |     2 |      30 |       0

or you can use arithmetic to calculate the values:

SELECT TRUNC( 24 * ( end_date - start_date ) ) AS hours,
       TRUNC( MOD( 24 * 60 * ( end_date - start_date ), 60 ) ) AS minutes,
       ROUND( MOD( 24 * 60 * 60 * ( end_date - start_date ), 60 ) ) AS seconds
FROM   table_name;

which outputs:

HOURS | MINUTES | SECONDS
----: | ------: | ------:
    2 |      30 |       0

db<>fiddle here

0
votes

Since you want a string value, an alternative based on your query attempt is to add the difference between your two date values (which is a numeric value, the number of days between them, including fractional days) to an arbitrary fixed date; and then convert the result of that to a string:

SELECT to_char(date '0001-01-01'
  + (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') - to_date( '2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')),
  'HH24:MI') as diff
FROM dual;

DIFF 
-----
02:30

If the difference can exceed 24 hours then you need to decide how to report that; if you want to include days as a separate figure then you can still use this approach, but need to subtract one (if your fixed date is the first) from the difference before formatting as a string:

SELECT to_char(date '0001-01-01'
  + (to_date('2009-07-08 22:00', 'YYYY-MM-DD hh24:mi') - to_date( '2009-07-07 19:30', 'YYYY-MM-DD hh24:mi'))
  - 1,
  'DDD:HH24:MI') as diff
FROM dual;

DIFF     
---------
001:02:30

If you want the 'hours' value to be higher instead - e.g. '26:30' in this example - then it gets rather more complicated; I see @MTO has added the 'arithmetic' approach already so I won't repeat that. But then might be better off going down the extract() route (which you should consider anyway as it's more flexible and elegant...)