Work with Data/Time in Postgres

A common scenario in Postgres (at least for me) is do conversions between different time formats. For example

* How to transform 'time' from epoch to any human readable format?

  SELECT to_char(to_timestamp(1239148800) at TIME ZONE 'UTC', 'YYYY/MM/DD HH24:MI:SS')

For any other format, you can see the table "Template Patterns for Date/Time Formatting" on the documentation of your version of Postgres.


* How to transform from timestamp to epoch ?

  SELECT EXTRACT (EPOCH FROM timestamp '20090101T041000' at time zone 'UTC') ;

Other way to do the last transformation is set the time zone on the environment

  SET TIMEZONE TO 'UTC'
  SELECT EXTRACT(EPOCH FROM timestamp '20090506T041000');