CODEWITHSUNDEEP
postgresqlintervalsepoch
David Garcia
David Garcia

Reputation: 2696

PostgreSQL: Column containing seconds to Hours Minutes Seconds Days INTERVAL SECONDSDIFF

I want to convert a column which contains seconds (i.e 11549404) into days, hours, minutes, seconds

SELECT (myCol || ' second')::interval, 'HH24 hrs MI "minutes" SS "seconds"') AS duration
FROM public.myTable

Which returns the following;

"3208 hrs 10 minutes 04 seconds"

Whats the way to display it as days, hours, minutes seconds

Upvotes: 0

Views: 696

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45750

Because some days has 23hours and others 25hours the result is not easy task (it is not possible, because don't know absolute value). interval type is a structure of months, days and seconds. The values are not automatically moved between these fields because mounts has different number of days, days can has different number of seconds. But you can do some normalization - there is a function justify_interval that expects so days has 24 hours every time:

postgres=# select justify_interval('3208 hrs 10 minutes 04 seconds'::interval);
+-------------------------+
|    justify_interval     |
+-------------------------+
| 4 mons 13 days 16:10:04 |
+-------------------------+
(1 row)

Upvotes: 1

Related Questions