find the elapsed time between two dates in oracle sql

Question

i need to find the difference between the time in the format hh:mm:ss

select msglog.id,max(msglog.timestamp) enddate,
   min(msglog.timestamp) startdate,
   enddate - startdate
   from MESSAGELOG msglog
   group by id

In the abovequery msglog.timestamp is of type DATE.

How can I get the elapsed time or diff between the time in the correct format in oracle?

Answer 1

When you subtract two DATE values like enddate - startdate you get the difference in days with decimal accuracy, so for example 1.5 would mean 1 1/2 days or 36 hours. You can convert that to HH:MI:SS using a lot of math, but an easier way is to convert the decimal value to an INTERVAL DAY TO SECOND value using the NUMTODSINTERVAL function:

  NUMTODSINTERVAL(enddate - startdate, 'DAY')

You'd think the TO_CHAR function would be able to format this as HH:MI:SS, but it doesn't seem to work that way. You can use EXTRACT instead, and TO_CHAR to make sure you get leading zeros:

 TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')

The 00 part of the format code specifies two digits, with a leading zero if needed. The FM part gets rid of the leading space in the formatted result, which is reserved for a negative sign if needed.

Also note that your query gets aggregate values and uses them in the same SELECT list. Oracle won't let you do this. Try something like this instead:

WITH StartEndByID AS (
  SELECT
    msglog.id,
    NUMTODSINTERVAL(max(msglog.timestamp) - min(msglog.timestamp), 'DAY') elapsed
  FROM messagelog msglog
  GROUP BY id
)
SELECT
  id,
  TO_CHAR(EXTRACT(HOUR FROM elapsed), 'FM00') || ':' ||
    TO_CHAR(EXTRACT(MINUTE FROM elapsed), 'FM00') || ':' ||
    TO_CHAR(EXTRACT(SECOND FROM elapsed), 'FM00') AS ElapsedHHMISS
FROM StartEndByID

Answer 2

Date arithmetic in Oracle results in a number expressed in days. So, to convert to hours, you would multiply by 24 and then trunc to get an integral number:

trunc(24 * (enddate - startdate))

To get minutes, convert the days value to minutes and mod() that with 60:

mod(trunc(24 * 60 * (enddate - startdate)), 60)

For seconds, again convert days to seconds and mod() that with 60:

mod(trunc(24 * 60 * 60 * (enddate - startdate)), 60)

Now you can put these together to get the string value you need.