PostgreSQL: How to Convert Seconds to HH:MM:SS

Often in PostgreSQL you may want to convert a column that contains seconds to hours, minutes and seconds in a HH:MM:SS format.

You can use the following basic syntax to do so:

SELECT *,
       task_time * interval '1 sec' AS new_time
FROM sales_data;

This particular example creates a new column named new_time that converts the number of seconds in the task_time column to hours, minutes and seconds in a HH:MM:SS format.

The following example shows how to use this syntax in practice.

Example: How to Convert Seconds to HH:MM:SS in PostgreSQL

Suppose that we create the following table named emp that contains information about the number of seconds that it took for various employees at a company to complete some task:

-- create table named 'sales'
CREATE TABLE sales_data (
  id INT  PRIMARY KEY,
  task_time INT
);

-- add rows to the table 
INSERT INTO sales_data VALUES (0001, 856);
INSERT INTO sales_data VALUES (0002, 270);
INSERT INTO sales_data VALUES (0003, 1377);
INSERT INTO sales_data VALUES (0004, 3741);
INSERT INTO sales_data VALUES (0005, 590);
INSERT INTO sales_data VALUES (0006, 3846);
INSERT INTO sales_data VALUES (0007, 3182);
INSERT INTO sales_data VALUES (0008, 6789);

-- view all rows in table
SELECT * FROM sales_data;

 id | task_time 
----+-----------
  1 |       856
  2 |       270
  3 |      1377
  4 |      3741
  5 |       590
  6 |      3846
  7 |      3182
  8 |      6789

The task_time column is currently an integer that represents the number of seconds it took for the employee to complete the task.

Suppose that we would like to display the seconds in this column in a HH:MM:SS format instead.

We can use the following syntax to do so:

SELECT *,
       task_time * interval '1 sec' AS new_time
FROM sales_data;

 id | task_time | new_time 
----+-----------+----------
  1 |       856 | 00:14:16
  2 |       270 | 00:04:30
  3 |      1377 | 00:22:57
  4 |      3741 | 01:02:21
  5 |       590 | 00:09:50
  6 |      3846 | 01:04:06
  7 |      3182 | 00:53:02
  8 |      6789 | 01:53:09

The resulting table contains a new column named new_time that converts the number of seconds in the task_time column to hours, minutes and seconds in a HH:MM:SS format.

You can use similar syntax to convert seconds to hours, minutes and seconds in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Calculate Difference Between Dates in Days
PostgreSQL: How to Calculate Difference Between Dates in Weeks
PostgreSQL: How to Calculate Difference Between Dates in Months