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

Question

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.

Craig Ringer · Accepted Answer · 2014-01-31 13:29:54Z

1

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;

edited Jan 31, 2014 at 13:29

answered Jan 31, 2014 at 8:28

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3243383 Over a year ago

i created this function but it is nly giving the seconds part and not displaying anythin "00:00:04" nly this as output am getting.

Craig Ringer Over a year ago

@user3243383 Well, without seeing the input, I can't tell you anything about the output. The second query is much better btw, don't use the first one.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related