Mar 282013
If you use the timestamptz data type, Postgres does timezone conversions automatically.
First, some test data:
1 2 3 4 5 6 7 8 9 10 11 12 | pg=> create table time_test (id text, stamp timestamptz);CREATE TABLEpg=> insert into time_test values('foo', now());INSERT 0 1pg=> insert into time_test values('foo', now());INSERT 0 1pg=> select * from time_test;id | stamp-----+-------------------------------foo | 2013-01-22 00:53:40.325041+00foo | 2013-01-22 00:54:02.021018+00(2 rows) |
Client-supplied data data in other timezones is automatically converted for comparisons:
1 2 3 4 5 | 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:
1 2 3 4 5 6 | pg=> select id, stamp at time zone 'PST' from time_test;id | timezone-----+----------------------------foo | 2013-01-21 16:53:40.325041foo | 2013-01-21 16:54:02.021018(2 rows) |
…once, or for the whole session.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | pg=> set session time zone "pst8pdt";SETpg=> select * from time_test;id | stamp-----+-------------------------------foo | 2013-01-21 16:53:40.325041-08foo | 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 1pg=> select * from time_test;id | stamp-----+-------------------------------foo | 2013-01-21 16:53:40.325041-08foo | 2013-01-21 16:54:02.021018-08bar | 2013-01-21 16:55:03-08(3 rows) |