Re: Best way to convert seconds to hours:minutes:seconds

From: John K. Hinsdale <hin_at_alma.com>
Date: 30 Dec 2006 12:24:36 -0800
Message-ID: <1167510276.829283.222980@v33g2000cwv.googlegroups.com>

Michel Cadot wrote:

> If your elapsed time does not exceed 24 hours (86400 seconds)
> you can use (with elapsed=81202 seconds):
>
> SQL> select to_char(to_date(81202,'SSSSS'),'HH24:MI:SS') elapsed from dual;

A simple extension to Michel's expression (which does the tricky part) will work for durations greater than 24 hours:

select decode(floor(999999/86400),

                 0, '',
                 floor(999999/86400) || ' day(s), ')
       || to_char(to_date(mod(999999, 86400),'SSSSS'),
                  'HH24:MI:SS') AS elapsed

from dual;

.. gives ...

ELAPSED

---

11 day(s), 13:46:39

SQL> Substitute the column name for 999999 above. Note the above gives an undesirable result for NULL and breaks entirely for values < 0 (!)

--JH Received on Sat Dec 30 2006 - 14:24:36 CST