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
> 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.
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;
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.