How to convert Number of hours into number of work-hours days in postgreSql?

i have a column which has data in "12:19:04" this format. i.e 12 hours 19 min and 4 sec. i want to convert this into number of days considering working hours as one day i.e 10 hr

so the above time should be displayed as 1 day 2 hr 19 min and 4 sec

and also if the hours value is too huge then month days hours and minutes should be displayed.

Kindly help me in this.

Answers


Easiest way: Use epoch time, divide by 10 days in seconds, convert remainder back to seconds.

WITH s(secs) AS (SELECT extract(epoch from interval '12:19:04'))
SELECT justify_interval(
         floor(secs / 36000) * INTERVAL '1' DAY 
       + (secs::integer % 36000) * INTERVAL '1' SECOND
) FROM s;

Need Your Help

Latest value = "Not Downloaded"

tfs2010

I have several projects in TFS which visually appear in the directory hierarchy as greyed out. When I browse within the projects in Visual Studio 2010/Source Control/TFS 2010, I notice that all of...