PostgreSQL: How to Convert Time to Seconds

Often in PostgreSQL you may want to convert a time interval to seconds.

You can use the EXTRACT and EPOCH statements to do so:

SELECT *,
       FLOOR(EXTRACT(EPOCH FROM task_time)) AS total_seconds
FROM emp;

This particular example creates a new column named total_seconds that converts the time interval from the task_time column in the emp table to a total number of seconds.

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

Example: How to Convert Time to Seconds in PostgreSQL

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

-- create table named 'emp'
CREATE TABLE emp (
  empID INT,
  task_time INTERVAL
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '00:15:00');
INSERT INTO emp VALUES (0002, '04:05:08');
INSERT INTO emp VALUES (0003, '07:00:33');
INSERT INTO emp VALUES (0004, '09:15:46');
INSERT INTO emp VALUES (0005, '12:56:17');

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

 empid | task_time 
-------+-----------
     1 | 00:15:00
     2 | 04:05:08
     3 | 07:00:33
     4 | 09:15:46
     5 | 12:56:17

The time in the task_time column is currently shown as hours, minutes and seconds.

We can use the following syntax to create a new column that converts the times from the task_time column to seconds:

SELECT *,
       FLOOR(EXTRACT(EPOCH FROM task_time)) AS total_seconds
FROM emp;

 empid | task_time | total_seconds 
-------+-----------+---------------
     1 | 00:15:00  |           900
     2 | 04:05:08  |         14708
     3 | 07:00:33  |         25233
     4 | 09:15:46  |         33346
     5 | 12:56:17  |         46577

The new column named total_seconds contains the times from the task_time column as total number of seconds.

For example:

• 15 minutes is equivalent to 900 seconds.
• 4 hours, 5 minutes and 8 seconds is equivalent to 14,708 seconds.
• 7 hours and 33 seconds is equivalent to 25,233 seconds.

And so on.

You can use similar syntax to convert a time to seconds in your own table.

Additional Resources

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

PostgreSQL: Select Rows where Timestamp is Empty
PostgreSQL: How to Select Records Between Two Timestamps
PostgreSQL: How to Remove Milliseconds from Timestamp