PostgreSQL update time zone offset

Question

What's a quick and easy way to correct the time zone TIMESTAMP WITH TIME ZONE fields accidentally entered in the wrong time zone?

In my case, the following records were mistakenly input in UTC rather than in US/Pacific:

           t0           |           t1           |     what
------------------------+------------------------+---------------
 2011-06-01 13:00:00+00 | 2011-06-01 13:10:00+00 | recalibrating
 2011-06-01 13:10:00+00 | 2011-06-01 13:45:00+00 | verifying
 2011-06-01 13:45:00+00 | 2011-06-01 13:55:00+00 | FAULT

Fortunately, none of the mistaken records cross daylight savings boundaries, so 2 o'clock UTC can simply be corrected as 2 o'clock Pacific.

Answer 1

UPDATE <table> SET <timestamptz_field> = (<timestamptz_field> AT TIME ZONE 'UTC') AT TIME ZONE '<correct_time_zone>';

Answer 2

There are casts and, more importantly, the at time zone operator, which are useful for this kind of thing, e.g.:

test=#  select now(),
               now()::timestamp;

              now              |            now             
-------------------------------+----------------------------
 2011-06-27 14:32:04.169292+02 | 2011-06-27 14:32:04.169292
(1 row)

test=# select now() at time zone 'utc',
             (now() at time zone 'utc')::timestamp with time zone;

          timezone          |           timezone            
----------------------------+-------------------------------
 2011-06-27 12:32:28.430479 | 2011-06-27 12:32:28.430479+02
(1 row)