0

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

David Garcia
  • 3,056
  • 18
  • 55
  • 90

1 Answers1

1

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)
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • how about with a column name? `SELECT (myCol || ' second')::interval, 'HH24 hrs MI "minutes" SS "seconds"')` – David Garcia Jun 26 '19 at 16:04
  • For column name you should use a keyword `AS` and if has some special symbols, then you should to use double qoutes `SELECT 10 AS "some special name with spaces" ...` – Pavel Stehule Jun 26 '19 at 16:10