psql timezones

query-with-pgtz.sql

-- $> PGTZ=PST8PDT psql
-- If the PGTZ environment variable is set in the frontend environment 
-- of a client based on libpq, libpq will automatically SET TIMEZONE to 
-- the value of PGTZ during connection start-up.

select
  created_at,
  created_at at time zone 'UTC' at_tz_utc,
  created_at at time zone 'PST' at_tz_pst,
  created_at at time zone 'PST' - interval '8 hours' at_tz_pst_minus_8_hours
from
  widgets
;

--         created_at         |           at_tz_utc           |           at_tz_pst           |    at_tz_pst_minus_8_hours    
------------------------------+-------------------------------+-------------------------------+-------------------------------
-- 2013-12-31 16:34:26.710356 | 2013-12-31 08:34:26.710356-08 | 2013-12-31 16:34:26.710356-08 | 2013-12-31 08:34:26.710356-08

query.sql

-- $> psql

select
  created_at,
  created_at at time zone 'UTC' at_tz_utc,
  created_at at time zone 'PST' at_tz_pst,
  created_at at time zone 'PST' - interval '8 hours' at_tz_pst_minus_8_hours
from
  widgets
;

--         created_at         |           at_tz_utc           |           at_tz_pst           |    at_tz_pst_minus_8_hours    
------------------------------+-------------------------------+-------------------------------+-------------------------------
-- 2013-12-31 16:34:26.710356 | 2013-12-31 16:34:26.710356+00 | 2014-01-01 00:34:26.710356+00 | 2013-12-31 16:34:26.710356+00

table-widgets.txt

                                                            Table "public.widgets"
       Column       |            Type             |                         Modifiers                          | Storage  |
--------------------+-----------------------------+------------------------------------------------------------+----------+
 id                 | integer                     | not null default nextval('contributions_id_seq'::regclass) | plain    |
 created_at         | timestamp without time zone | not null                                                   | plain    |

So we see that the widget was created at 16:34 UTC, which is 8:34AM PST, because we are in PST right now. So is using PGTZ the correct thing to do, and then spin the timestamp back to UTC? Will this handle PDT when we shift?

Where this bit me was when I was running an EOY report. I thought timezone would actually switch the timezone, but looking at it now I see I was wrong.

Basically I want to make sure I can get all widgets that were created in 2014 even though some might have been created in NY, some in Denver, some in LA, and some in Hawaii.

The column 'created_at' is currently 'timestamp without time zone' You need to use timestamp *with* time zone to get this all correct.