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');

Generating lines from one line

If you want to generate lines from one line according to a field

You can do in perl with:

$ echo "1,a/b/c/d" | \
perl -F, -l0ane 'print "$F[0],$_\n" foreach(split("/",$F[1]))'
$

Or if you want to write less do:

$ echo "1,a/b/c/d" | awk -F, '{split($2,z,"/");for(i in z)print$1","z[i]}'