Often in PostgreSQL you may want to convert a timestamp column to an integer.
You can use the following basic syntax to do so:
SELECT *,
extract(epoch FROM start_time)::int AS start_time_int
FROM emp;
This particular example converts the timestamp in the start_time column of the table named emp to an integer value 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 timestamp in the start_time column.
We then use the extract statement to extract this value.
Lastly, we cast the result to an integer by using the ::int statement.
The following example shows how to use this syntax in practice.
Example: How to Convert Timestamp to Integer 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 timestamp in the start_time column to an integer.
We can use the following syntax to do so:
SELECT *,
extract(epoch FROM start_time)::int AS start_time_int
FROM emp;
empid | start_time | sales | start_time_int
-------+---------------------+-------+----------------
1 | 2022-09-23 04:15:00 | 134 | 1663906500
2 | 2023-04-01 04:05:00 | 201 | 1680321900
3 | 2024-10-26 07:00:00 | 155 | 1729926000
4 | 2024-12-14 09:15:00 | 98 | 1734167700
5 | 2024-12-07 12:56:00 | 67 | 1733576160
The resulting table contains a new column named start_time_int that contains the timestamp from the start_time column converted to an integer.
Feel free to use similar syntax to convert a timestamp in a column of your own table to an integer.
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
