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.
Table of Contents
TO_TIMESTAMP Syntax
Below is the syntax of the TO_TIMESTAMP function.
TO_TIMESTAMP(double) -> timestamp with time zoneIt 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.
SELECT TO_TIMESTAMP(1704058224);
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.
SELECT TO_TIMESTAMP(1704058224)::TIMESTAMP
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';
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.
Visit the official PostgreSQL website to get a list of time zones along with their UTC offsets.
Summary
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