Convert Epoch to a Timestamp in Postgres

Epoch time, also known as Unix Epoch, refers to Epoch which is commonly used to represent the dates and times in computing. Converting the Epoch to a timestamp involves adding the number of seconds since the Epoch to the base timestamp.

This tutorial covers the basic methods and techniques to convert an Epoch time to a Unix timestamp.

Convert Epoch to Timestamp in PostgreSQL

The most common method of converting an epoch time to a timestamp equivalent is using PostgreSQL’s to_timestamp() function.

The function takes two arguments: the number of seconds since the Epoch and an optional target timezone.

The syntax is as demonstrated in the following:

Let us take a look at a basic example that converts a given epoch time to a timestamp using the PostgreSQL to_timestamp() function.

In the previous example, we use the to_timestamp() function to convert the provided elapsed epoch seconds to timestamp. The resulting value is as follows:

To convert the given epoch value to a given timezone, we can specify the target timezone as shown in the following:

This should return the timestamp equivalent in the specified timezone. However, it is good to keep in mind that PostgreSQL may fail to automatically convert the input epoch time to timestamp using a target timezone without type casting.

In such a case, we can combine the to_char function as shown in the following:

Resulting Output:

By default, the TO_TIMESTAMP() function returns the timestamp in the “YYYY-MM-DD HH:MI: SS” format . However, we can customize the timestamp format using the to_char() function.

Output:

Conclusion

We explored using the to_timestamp() function to convert a given epoch value to a timestamp value. Again, you can refer to the documentation to learn more.