PostgreSQL: Convert Epoch to Timestamp

Epoch time is an industry standard and is used in various API’s and applications. The epoch time is the number of seconds that have passed since January 1, 1970, at 00:00:00 UTC. Epoch time is consistent across various computer systems and is not affected by time zone differences. It only represents a single number. PostgreSQL provides the TO_TIMESTAMP() function to easily convert an epoch to a timestamp.

In this post, we’ll learn various techniques in PostgreSQL to correctly convert epoch time to a timestamp value.


Below is the syntax of the TO_TIMESTAMP function.

TO_TIMESTAMP(double) -> timestamp with time zone

It takes epoch time as a double number and returns the timestamp with the time zone.

Convert Epoch to Timestamp Example

In the below example, we are converting epoch time to a timestamp with a time zone.

Convert Epoch time to timestamp

TO_TIMESTAMP convert epoch time to timestamptz data type not timestamp data type.

To convert it to only timestamp data type without time zone, cast timestamptz to timestamp data type as shown below.

Convert epoch to timestamp without time zone

Convert Epoch to Timestamp with Specific Time Zone Example

To convert the epoch timestamp to a specific time zone date time, use the “AT TIME ZONE” operator. The AT TIME ZONE operator converts timestamp values to different time zones.

SELECT TO_TIMESTAMP(1704058224) AT TIME ZONE 'Asia/Tokyo';
Convert epoch time to time zone timestamp

To find the different time zones supported by the PostgreSQL database, check the pg_timezone_names table. There are a total of 1196 time zones supported by PostgreSQL.

Time zones supported by PostgreSQL

Visit the official PostgreSQL website to get a list of time zones along with their UTC offsets.


Epoch is a standard format for providing accurate time without worrying about the time zones. PostgreSQL’s TO_TIMESTAMP() function helps convert epoch time to a timestamp value. Use the “AT TIME ZONE” operator if you want to convert an epoch to a specific time zone.

Related Posts