How to convert Unix timestamp to Datetime in PostgreSQL

🎈 🎈 🎈


    SELECT TO_TIMESTAMP(UnixTimestamp) AS ConvertedDate
    FROM your_table;

Output:

--------------------------- | ConvertedDate | --------------------------- | 2020-02-29 11:23:03 |

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

Code Explanation

In this PostgreSQL query, the TO_TIMESTAMP() function takes the UnixTimestamp, for example 1582975383, as an argument and converts it into a timestamp.
The resulting date and time, for example 2020-02-29 11:23:03, are then returned in a new column labeled as ConvertedDate.

Convert Date to Unix timestamp 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