PostgreSQL: How to Convert Date to Unix Timestamp

Often in PostgreSQL you may want to convert a datetime column to a Unix timestamp.

You can use the following basic syntax to do so:

SELECT *,
       extract(epoch FROM start_time) AS start_time_unix
FROM emp;

This particular example converts the datetime in the start_time column of the table named emp to a Unix timestamp and saves the results to a column named start_time_int.

Note that we use the epoch statement to return the number of seconds since January 1, 1970 for the datetime in the start_time column.

We then use the extract statement to extract this value.

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

Example: How to Convert Date to Unix Timestamp in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees at some company started working on a specific task:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT  PRIMARY KEY,
  start_time TIMESTAMP
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2022-09-23 04:15:23');
INSERT INTO emp VALUES (0002, '2023-04-01 04:05:56');
INSERT INTO emp VALUES (0003, '2024-10-26 07:00:10');
INSERT INTO emp VALUES (0004, '2024-12-14 09:15:00');
INSERT INTO emp VALUES (0005, '2024-12-07 12:56:30');

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

 empid |     start_time      
-------+---------------------
     1 | 2022-09-23 04:15:23
     2 | 2023-04-01 04:05:56
     3 | 2024-10-26 07:00:10
     4 | 2024-12-14 09:15:00
     5 | 2024-12-07 12:56:30

Suppose that we would like to convert each datetime in the start_time column to a Unix timestamp.

We can use the following syntax to do so:

SELECT *,
       extract(epoch FROM start_time) AS start_time_unix
FROM emp;

 empid |     start_time      |  start_time_unix  
-------+---------------------+-------------------
     1 | 2022-09-23 04:15:23 | 1663906523.000000
     2 | 2023-04-01 04:05:56 | 1680321956.000000
     3 | 2024-10-26 07:00:10 | 1729926010.000000
     4 | 2024-12-14 09:15:00 | 1734167700.000000
     5 | 2024-12-07 12:56:30 | 1733576190.000000

The resulting table contains a new column named start_time_unix that contains the datetime from the start_time column represented as a Unix timestamp.

For example, we can see:

• 2022-09-23 04:15:23 is equivalent to 1663906523 as a Unix timestamp.
• 2023-04-01 04:05:56 is equivalent to 1680321956 as a Unix timestamp.
• 2024-10-26 07:00:10 is equivalent to 1729926010 as a Unix timestamp.

And so on.

You can use similar syntax to convert a datetime column in your own table to a Unix timestamp.

Additional Resources

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

PostgreSQL: Calculate Timestamp Difference in Hours
PostgreSQL: Calculate Timestamp Difference in Minutes
PostgreSQL: Calculate Difference Between Dates in Days