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.

GG. · Accepted Answer · 2021-05-04 17:05:48Z

24

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

edited May 4, 2021 at 17:05

GG.

22k14 gold badges93 silver badges133 bronze badges

answered Jun 27, 2011 at 12:34

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis de Bernardy · Accepted Answer · 2011-06-27 12:33:45Z

6

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)

answered Jun 27, 2011 at 12:33

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

PostgreSQL update time zone offset

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related