How to convert Unix​ epoch time to timestamps in PostgreSQL

Today we will see how to convert the Unix epoch strings to timestamps in PostgreSQL, directly. Let’s say we are storing the Unix epoch time in a database in the timestamp column.

> select timestamp from events;

Output:

timestamp   | 
------------+
 1591876140
 1591876200
 1591876260
 1591876320
 1591876380

Problem

When querying the database, timestamps in this format are not very useful as we can’t figure out which date and time the string is representing. If we could get a proper date and time representation, it will be easier to debug the data.PostgreSQL has a built-in function to_timestamp() that can be used for this exact purpose.

prathamesh=# select to_timestamp(1591876380);

Output:

       to_timestamp
    --------------------
 2020-06-11 17:23:00+05:30

Specific Timezone

As you can see, it returns the timestamp in the system’s timezone. In my case, it returns the timestamp in IST. If you want the timestamp in a different timezone, you can easily do that using the timezone function.

prathamesh=# select timezone('America/New_york', to_timestamp(1591876380));

Output:

        timezone
    -----------------
   2020-06-11 07:53:00
   (1 row)

We can convert the previous query as follows:

> select timezone('America/New_york', to_timestamp(timestamp) from events;

The to_timestamp() function also accepts a second argument that decides the format of the output. The complete list of formats can be found here.

You can visit my earlier post about converting Unix epoch strings to Ruby objects. This is useful when converting timestamps to Ruby objects in a Ruby on Rails application.