Converting decimal seconds to string

Like many others out there, I often have a need to convert a decimal seconds value to its canonical equivalent as a string. Which this can be done using standard SQL I prefer to wrap the required conversion functions inside a function as follows.

 CREATE OR REPLACE FUNCTION SS2HMS (p_seconds IN NUMBER,
                                      p_hr      IN VARCHAR2 DEFAULT ':',
                                      p_min     IN VARCHAR2 DEFAULT ':',
                                      p_sec     IN VARCHAR2 DEFAULT '')
 RETURN VARCHAR2 
 AS 
 BEGIN
     RETURN to_char(trunc(p_seconds/3600),        'FM999999990')  || p_hr  || 
             to_char(trunc(mod(p_seconds,3600)/60),'FM00')         || p_min || 
             to_char(      mod(p_seconds,60),      'FM00')         || p_sec;
 END SS2HMS;

Which can be called as follows:

 select SS2HMS(13413.308) as HMS from dual
 union all
 select SS2HMS(13413.308,'h','m','s') as HMS from dual;
HMS
3:43:33
3h43m33s

I hope this is of help to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *