Extract days hours minutes seconds from two dates in oracle:
Example 1:
SQL> SELECT numtodsinterval(sysdate - (sysdate-5.2934), 'day') as diff
FROM dual;
DIFF
---------------------------------------------------------------------------
+000000005 07:02:30.000000000
Example 2:
CREATE OR REPLACE
FUNCTION date_diff
(date1
DATE, date2
DATE)
RETURN VARCHAR2
AS
temp VARCHAR2
(128);
BEGIN
SELECT
floor(date1 - date2
)
||
' DAYS '
|| MOD
(FLOOR ((date1 - date2
) *
24),
24)
||
' HOURS '
|| MOD
(FLOOR ((date1 - date2
) *
24 *
60),
60)
||
' MINUTES '
|| MOD
(FLOOR ((date1 - date2
) *
24 *
60 *
60),
60)
||
' SECS ' time_difference
INTO temp
FROM DUAL;
RETURN temp;
END;
SQL> SELECT date_diff(sysdate, sysdate-5.2934) diff
FROM dual;
DIFF
--------------------------------------------------------------------------------
5 DAYS 7 HOURS 2 MINUTES 30 SECS