How to convert Datetime to Unix timestamp in PostgreSQL

🎈 🎈 🎈


    SELECT EXTRACT(EPOCH FROM date_column) AS UnixTimestamp
    FROM your_table;

Output:

----------------- | UnixTimestamp | ----------------- | 1514901056 |

Example only. There may be multiple ways to perform this operation.

Code Explanation

In this PostgreSQL query, the EXTRACT(EPOCH FROM date_column) function is used to convert a date or timestamp (for example '2018-01-02 13:50:56') into a Unix timestamp. The EPOCH keyword tells PostgreSQL to return the number of seconds that have elapsed since the Unix epoch (January 1, 1970) for the given date in 'date_column'.
The result is a numeric value representing the Unix timestamp, which is then aliased as 'UnixTimestamp' in the output.

Convert Unix timestamp to Date in PostgreSQL

Other useful PostgreSQL date functions

Function	Description	Example
`NOW()`	Returns the current date and time.	`NOW()` returns 2024-08-28 12:34:56.789
`CURRENT_DATE`	Returns the current date.	`CURRENT_DATE` returns 2024-08-28
`CURRENT_TIME`	Returns the current time.	`CURRENT_TIME` returns 12:34:56.789
`AGE()`	Calculates the interval between the current date and a given timestamp.	`AGE(TIMESTAMP '2020-01-01')` returns 4 years 7 months 27 days
`TO_CHAR()`	Formats a date or timestamp to a string according to the specified format.	`TO_CHAR(NOW(), 'YYYY-MM-DD')` returns 2024-08-28
`TO_TIMESTAMP()`	Converts Unix epoch time to a timestamp.	`TO_TIMESTAMP(1693227296)` returns 2024-08-28 12:34:56
`EXTRACT()`	Extracts a specific part of a date (e.g., year, month, day).	`EXTRACT(YEAR FROM NOW())` returns 2024
`DATE_TRUNC()`	Truncates a date or timestamp to the specified precision (e.g., hour, day, month).	`DATE_TRUNC('month', NOW())` returns 2024-08-01 00:00:00
`JUSTIFY_DAYS()`	Adjusts intervals so that the days part is within normal range.	`JUSTIFY_DAYS(INTERVAL '40 days')` returns 1 month 10 days
`MAKE_DATE()`	Creates a date from year, month, and day fields.	`MAKE_DATE(2024, 8, 28)` returns 2024-08-28
`INTERVAL`	Represents a time interval, such as '1 day' or '2 hours'.	`NOW() + INTERVAL '1 day'` returns 2024-08-29 12:34:56.789

Current Unix Timestamp SECONDS SINCE JAN 01 1970

Current UTC Time

2025-08-20