René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Converting seconds to hours, minutes and seconds

How many hours, minutes and seconds are 10000 seconds? 
select to_char(to_date(10000,'sssss'),'hh24:mi:ss') from dual;

02:46:40
Verifying the result: 
select 2*60*60 + 46*60 + 40 from dual;

10000

Including days

The previous statement does not work for seconds greater or equal to 86400 (which is how many seconds there are in a day). The following statement does is able to convert any seconds (greater or equal to zero) to days, hours, minutes and seconds: 
select                 floor(12345678/86400) || 'd ' || 
       to_char(to_date(mod  (12345678,86400) ,'sssss'),'hh24"h" mi"m" ss"s"') 
from dual;

142d 21h 21m 18s
Verifying... 
select 142 * 24 * 60 * 60 +
        21      * 60 * 60 +
        21           * 60 +
        18
from dual;

12345678