convert seconds to dd:hh:mm
746179May 26 2011 — edited May 26 2011How can I convert seconds to the format dd:hh:mm (days:hours:minutes)? days should have at least 2 digits (e.g. 03 or 123) and hours should have always 2 digits and the maximum value should be 24 and minutes should have always 2 digits and the maximum value should be 59.
select
case when (trunc(6000/86400))<10 then to_char(trunc(6000/86400), '09') else to_char(trunc(6000/86400)) end
||':'||ltrim(to_char(trunc(mod(6000,86400)/3600),'09'))||':'||ltrim(to_char(mod(mod(6000,86400),60),'09')) as "dd:hh:mm"
from dual
--> does not work. It should give me 00:01:40 back but I get only 00:01:00.
Can someone help?