PostgreSQL update time zone offset

问题:

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.

回答1:

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

回答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)