DateTime Diff Query

Sample query for finding out the difference between two ORACLE time stamp fields


SELECT PROCESS_INSTANCE, EMPLID, BUSINESS_UNIT,
WF_FROM_DTTM, WF_TO_DTTM,
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600)/60)
|| ' MINS ' ||
ROUND((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600 -
(FLOOR((((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60) -
FLOOR(((WF_TO_DTTM-WF_FROM_DTTM)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' AS "RUN TIME"
FROM PS_SCB_JUP_REQ_ACT WHERE SCB_JUP_APR_TYPE = 'CFP'