SQL query to find difference between two time data

[jay_l_a]

bhattnirav offers an excellent solution.

This achieves the same, but in a different way.


CREATE OR REPLACE FUNCTION j_get_time_interval (p_start_time IN DATE, p_end_time IN DATE)
RETURN VARCHAR2 IS
nTimeIntervalInSeconds NUMBER;

BEGIN

SELECT (p_end_time - p_start_time) * 24*60*60
INTO nTimeIntervalInSeconds
FROM dual;

RETURN TO_CHAR(TRUNC(nTimeIntervalInSeconds/60/60),'09') ||'hrs'||
TO_CHAR(TRUNC(MOD(nTimeIntervalInSeconds,3600)/60),'09') ||'mins'||
TO_CHAR(MOD(MOD(nTimeIntervalInSeconds,3600),60),'09')||'secs';

END j_get_time_interval;
/