Convert seconds to HH:MM:SS PostgreSQL

Here’s how to convert a numeric integer field of seconds to HH:MM:SS. Use the INTERVAL PostgreSQL data type. The INTERVAL data type is used to store and manipulate a time period.

Example

SELECT 120 * interval '1 sec';

Output

|120 seconds to HH:MM:SS|
|-----------------------|
|00:02:00|

An example with a table of rows of call records that have the length of call in seconds in a column called duration.

select duration
, duration * interval '1 sec' seconds_to_hh_mm_ss
, duration * interval '1 min' min_to_hh_mm_ss
from calls;

Output

|duration|seconds_to_hh_mm_ss|min_to_hh_mm_ss|
|--------|-------------------|---------------|
|1531|00:25:31|25:31:00|
|1962|00:32:42|32:42:00|
|1213|00:20:13|20:13:00|
|556|00:09:16|09:16:00|
|1382|00:23:02|23:02:00|
|2622|00:43:42|43:42:00|
|3390|00:56:30|56:30:00|