How to convert number of seconds into Oracle date format ( Days Hours :Minutes : Seconds )

How to convert number of seconds into Oracle date format (Days Hours:Minutes:Seconds):

SELECT TO_CHAR(TO_DATE(ROUND('<variable>'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

SELECT ROUND('<variable>'/'86399') || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('<variable>','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Replace <variable> with number of seconds. ROUND function is used to round to nearest integer if you provide seconds in a decimal format

Example1: 

SELECT TO_CHAR(TO_DATE(ROUND('3000'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

------------------------------

00:50:00

3000 seconds is displayed in the hours, minutes and seconds format: read as 50 minutes

Example2: 

SELECT TO_CHAR(TO_DATE(ROUND('30007.345'), 'SSSSS'), 'HH24:MI:SS') Hours_Minutes_Format

FROM DUAL;

Output:

Hours_Minutes_Format

------------------------------

08:20:07

30007.345 seconds is displayed in the hours, minutes and seconds format: read as 8 hours 20 minutes and 7 seconds

Example3: 
Above examples cover the seconds for a day. Lets take a large number of seconds which goes beyond a day.  Below query provides the output for 90740 seconds

SELECT ROUND('90740'/86399) || ' Day(s) ' || TO_CHAR(TO_DATE(ROUND(MOD('90740','86399')), 'SSSSS'), 'HH24:MI:SS') Days_Hours_Minutes_Format

FROM DUAL;

Output:

Days_Hours_Minutes_Format

---------------------------------------

1 Day(s) 01:12:21

90740 seconds is displayed in the days, hours, minutes and seconds format: read as 1 Day 1 hours 12 minutes and 21 seconds

Tip: 

This is very useful if you are trying to get the run time of a concurrent request in a readable format in Oracle Applications. Sample query below

SELECT request_id, (actual_completion_date - actual_start_date)*24*60*60 run_time_secs,

       TO_CHAR(TO_DATE((actual_completion_date - actual_start_date)*24*60*60, 'SSSSS'), 'HH24:MI:SS') run_time_Hours_Minutes_Format

FROM fnd_concurrent_requests

WHERE request_id  = 89110175;

Sample Output:

***Please leave a comment***