Convert PostgreSQL timestamp without time zone to timestamp at UTC with ISO 8601 notation

There is a lot of hair-splitting on the subject, but the ISO-8601 standard allows for many variations in format. The most widely used places characters within the timestamp to indicate where the time portion of the stamp begins, and the time zone used — e.g. ‘2010-07-07T20:01:32Z’ Postgresql doesn’t support this exact format, so some manipulation has to be done. This does the trick:

select to_char(timezone(‘zulu’, to_timestamp(date_part(‘epoch’, my_timestamp_column))),’YYYY-MM-DDThh24:MI:SSZ’) as my_colname from my_table;

Important: this is PostgreSQL-specific code (8.4.5, at time of this post) — i.e. not standard SQL.

Advertisements
This entry was posted in PostgreSQL and tagged , , , , . Bookmark the permalink.