{"id":257,"date":"2013-03-28T23:23:32","date_gmt":"2013-03-29T06:23:32","guid":{"rendered":"http:\/\/www.lorrin.org\/blog\/?p=257"},"modified":"2013-03-28T23:23:32","modified_gmt":"2013-03-29T06:23:32","slug":"postgres-timezone-handling","status":"publish","type":"post","link":"https:\/\/www.lorrin.org\/blog\/2013\/03\/28\/postgres-timezone-handling\/","title":{"rendered":"Postgres timezone handling"},"content":{"rendered":"

If you use the timestamptz<\/code> data type, Postgres does timezone conversions automatically.<\/p>\n

First, some test data:<\/p>\n

pg=> create table time_test (id text, stamp timestamptz);\r\nCREATE TABLE\r\npg=> insert into time_test values('foo', now());\r\nINSERT 0 1\r\npg=> insert into time_test values('foo', now());\r\nINSERT 0 1\r\npg=> select * from time_test;\r\nid | stamp\r\n-----+-------------------------------\r\nfoo | 2013-01-22 00:53:40.325041+00\r\nfoo | 2013-01-22 00:54:02.021018+00\r\n(2 rows)<\/pre>\n
Client-supplied data data in other timezones is automatically converted for comparisons:<\/p>\n
pg=> select * from time_test where stamp > '2013-01-21 16:54:00 PST';\r\nid | stamp\r\n-----+-------------------------------\r\nfoo | 2013-01-22 00:54:02.021018+00\r\n(1 row)<\/pre>\n
Results can be converted on the fly:<\/p>\n
pg=> select id, stamp at time zone 'PST' from time_test;\r\nid | timezone\r\n-----+----------------------------\r\nfoo | 2013-01-21 16:53:40.325041\r\nfoo | 2013-01-21 16:54:02.021018\r\n(2 rows)<\/pre>\n
…once, or for the whole session.<\/p>\n
pg=> set session time zone \"pst8pdt\";\r\nSET\r\npg=> select * from time_test;\r\nid | stamp\r\n-----+-------------------------------\r\nfoo | 2013-01-21 16:53:40.325041-08\r\nfoo | 2013-01-21 16:54:02.021018-08\r\n(2 rows)\r\n\r\npg=> insert into time_test values ('bar', '2013-01-21 16:55:03');\r\nINSERT 0 1\r\npg=> select * from time_test;\r\nid | stamp\r\n-----+-------------------------------\r\nfoo | 2013-01-21 16:53:40.325041-08\r\nfoo | 2013-01-21 16:54:02.021018-08\r\nbar | 2013-01-21 16:55:03-08\r\n(3 rows)<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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 | […]<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[49],"tags":[45,75],"_links":{"self":[{"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/posts\/257"}],"collection":[{"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/comments?post=257"}],"version-history":[{"count":1,"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/posts\/257\/revisions"}],"predecessor-version":[{"id":258,"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/posts\/257\/revisions\/258"}],"wp:attachment":[{"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/media?parent=257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/categories?post=257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.lorrin.org\/blog\/wp-json\/wp\/v2\/tags?post=257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}