Printing time elapsed between two dates in Oracle

If your a command line kind of person and use SQL*Plus instead of Toad or SQL Developer (Raptor?), you probably know that you can use the following command to print out the elapsed time it took to execute your query.
"Set timing on"

After setting timing on, you get a simple output line telling you how long something took:
i.e.

SQL>select user from dual;

USER
------------------------------
RICH

Elapsed: 00:00:00.01
SQL>



I really like that silly little "elapsed" string, I kind of live and breath by it now since I've been looking at it for all these years. With this in mind, I created a little PL/SQL function that you can pass in two dates to get the elapsed time in a similar format (without the sub-seconds unfortunately). It uses the "extract" function, pretty neat.


CREATE OR REPLACE FUNCTION fx_elapsed_string(
p_start DATE,
p_end DATE)
RETURN VARCHAR2 IS
lv_big DATE;
lv_small DATE;
lv_string VARCHAR2(120);
BEGIN
IF p_start > p_end
THEN lv_big := p_start;
lv_small := p_end;
ELSE lv_big := p_end;
lv_small := p_start;
END IF;

SELECT (extract(day from
((lv_big - lv_small) day to second))*24) +
extract(hour from
((lv_big - lv_small) day to second))||':'||
substr('0'||extract(minute from
((lv_big - lv_small) day to second)), -2)||':'||
substr('0'||extract(second from
((sysdate - lv_small) day to second)), -2)
INTO lv_string
FROM DUAL;

RETURN lv_string;

EXCEPTION
WHEN others THEN RAISE;
END;
/
show errors


here's an example of me using it:

SQL>SELECT fx_elapsed_string(trunc(sysdate), sysdate) elapsed
from dual;

ELAPSED
------------------------------
18:13:14

Elapsed: 00:00:00.03
SQL>


In a nutshell, just a new way to print out the difference between two dates.

Until next time...Rich

Comments

Javier said…
Great this really solved my problem... thanks for sharing knowledge
This is awesome! Solved a huge problem for me. Thanks for posting this code.