The EPOCH time represents the number of seconds elapsed since "1st January 1970 00:00:00" until the present. The EPOCH time shows the DateTime in seconds, which is not easily understandable. To get the EPOCH time in appropriate DateTime representation, it must be converted into a human-readable format. To convert the EPOCH time to timestamp, the TO_TIMESTAMP() function is used in Postgres.

This post presents an in-depth overview of converting the EPOCH time to a timestamp using the TO_TIMESTAMP() function.

How to Convert EPOCH Time to Timestamp Using TO_TIMESTAMP()?

To convert the epoch seconds to appropriate DateTime, pass the EPOCH time as an argument to the TO_TIMESTAMP() function:

TO_TIMESTAMP(epoch_time);

The below-provided examples will help you understand epoch-to-timestamp conversion in a better way.

Example: Converting EPOCH Time to Timestamp Using TO_TIMESTAMP()

Let’s pass a specific epoch time to the TO_TIMESTAMP() function to convert it into a timestamp:

SELECT TO_TIMESTAMP(1231201120);

The given epoch has been successfully converted into a timestamp. The timestamp is retrieved based on the system’s timezone.

How to Convert EPOCH Time to Timestamp With a Specific Timezone?

Use the “TO_TIMESTAMP()” with the collaboration of the “TIMEZONE()” function to get a converted timestamp in a different timezone:

SELECT TIMEZONE('specific_timezone', TO_TIMESTAMP(epoch_time));

Example: Converting an EPOCH to Specific Timezone

The “specific_timezone” parameter must be replaced with one of the Postgres-supported timezones. To get the list of Postgres-supported timezones use the built-in “pg_timezone_names” table, as shown in the following snippet:

SELECT * FROM pg_timezone_names;

Suppose we wanted to convert the EPOCH to a timestamp based on the “Africa/Abidjan” time zone. To accomplish this task, we will execute the following command:

SELECT TIMEZONE('Africa/Abidjan', TO_TIMESTAMP(1231201120));

The given EPOCH time has been successfully converted into the specified timezone.

Conclusion

The EPOCH time shows the DateTime in seconds, which is not easily understandable. To get the EPOCH time in appropriate DateTime representation, it must be converted into a human-readable format. In Postgres, to convert the epoch time to a timestamp, pass the EPOCH time as an argument to the TO_TIMESTAMP() function. Use the “TO_TIMESTAMP()” with the collaboration of the “TIMEZONE()” function to get a converted timestamp in a different timezone. This post presented a detailed guide on how to convert the EPOCH time to timestamp using the TO_TIMESTAMP() function.