Postgres timezone handling

If you use the timestamptz data type, Postgres does timezone conversions automatically.

First, some test data:

pg=> create table time_test (id text, stamp timestamptz);
CREATE TABLE
pg=> insert into time_test values('foo', now());
INSERT 0 1
pg=> insert into time_test values('foo', now());
INSERT 0 1
pg=> select * from time_test;
id | stamp
-----+-------------------------------
foo | 2013-01-22 00:53:40.325041+00
foo | 2013-01-22 00:54:02.021018+00
(2 rows)

Client-supplied data data in other timezones is automatically converted for comparisons:

pg=> select * from time_test where stamp > '2013-01-21 16:54:00 PST';
id | stamp
-----+-------------------------------
foo | 2013-01-22 00:54:02.021018+00
(1 row)

Results can be converted on the fly:

pg=> select id, stamp at time zone 'PST' from time_test;
id | timezone
-----+----------------------------
foo | 2013-01-21 16:53:40.325041
foo | 2013-01-21 16:54:02.021018
(2 rows)

…once, or for the whole session.

pg=> set session time zone "pst8pdt";
SET
pg=> select * from time_test;
id | stamp
-----+-------------------------------
foo | 2013-01-21 16:53:40.325041-08
foo | 2013-01-21 16:54:02.021018-08
(2 rows)

pg=> insert into time_test values ('bar', '2013-01-21 16:55:03');
INSERT 0 1
pg=> select * from time_test;
id | stamp
-----+-------------------------------
foo | 2013-01-21 16:53:40.325041-08
foo | 2013-01-21 16:54:02.021018-08
bar | 2013-01-21 16:55:03-08
(3 rows)