Converting timestamps in PostgreSQL (again)

Revisited converting timestamps to UTC from different timezones. One way to do this is a variation on the PostgreSQL documentation on date/time functions, where epoch is used as a common unit:

select timestamp without time zone 'epoch' + (select extract(epoch from timestamp without time zone '2006-07-03 10:20:00' at time zone 'US/Eastern')) * interval '1 second' as timestamp_zulu;

Gets the job done, but the following is cleaner:

select (select '2012-08-01 03:00:00'::timestamp with time zone EDT) at time zone 'zulu';

And for insert:

insert into wq_obs (wq_timestamp) values ((select (select '2012-08-01 03:00:00'::timestamp with time zone EDT) at time zone 'zulu'));
Advertisements
This entry was posted in PostgreSQL and tagged , , . Bookmark the permalink.