Converting 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 database in the timestamp column.

> select timestamp from events;

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

When querying the database, timestamps in this format are not very useful as we can't figure out which date and time this string is representing.

If we could get a date and time representation, it will be easier to debug the data.

PostgreSQL has to_timestamp function which can be used for this exact purpose.

prathamesh=# select to_timestamp(1591876380);
       to_timestamp
---------------------------
 2020-06-11 17:23:00+05:30
(1 row

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

prathamesh=# select timezone('America/New_york', to_timestamp(1591876380));
      timezone
---------------------
 2020-06-11 07:53:00
(1 row)

The earlier query foe events table can be changes as follows.

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

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

---

I had written a post earlier about converting unix epoch strings to Ruby objects earlier. This is useful to convert timestamps to Ruby objects in a Ruby on Rails application.