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.
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;