Calculating Elapsed Time in Oracle

by Granville Bonyata on March 21, 2013

A frequent report request is the difference between a start and end time, expressed as HH:MI:SS. Rather than mess around with all sorts of SUBSTR, TO_CHAR and TO_DATE mishmash, I like to take advantage of Oracle’s powerful date manipulation:

Simply turn the difference between the dates into an integer (a fraction of a day), add it to a TRUNC of a date, and then display the hours, minutes, seconds portion of that date.

SELECT TO_CHAR(TRUNC(SYSDATE)+(end_date – start_date),’HH24:MI:SS’) run_time
FROM job_log
/

Let’s see it in action. We’ll load up a table with start and end dates and look at the results:

create table job_log
(start_date DATE,
end_date DATE)
/

insert into job_log
values (sysdate – (1.1/24), sysdate)
/

insert into job_log
values (sysdate – (12.2/24), sysdate)
/

insert into job_log
values (sysdate – (6.567/24), sysdate)
/

SELECT start_date,
end_date,
TO_CHAR(TRUNC(SYSDATE)+(end_date – start_date),’HH24:MI:SS’) difference
FROM job_log
/
START_DATE END_DATE DIFFEREN
——————- ——————- ——–
03/28/2013 06:46:55 03/28/2013 07:52:55 01:06:00
03/27/2013 19:40:55 03/28/2013 07:52:55 12:12:00
03/28/2013 01:18:54 03/28/2013 07:52:55 06:34:01

Previous post:

Next post: