Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 662
pilcrow Avatar asked Jun 27 '11 12:06

pilcrow


People also ask

How do you offset time zones?

A zone offset is the difference in hours and minutes between a particular time zone and UTC. In ISO 8601, the particular zone offset can be indicated in a date or time value. The zone offset can be Z for UTC or it can be a value "+" or "-" from UTC.


2 Answers

UPDATE <table>
SET <timestamptz_field> = 
  (<timestamptz_field> AT TIME ZONE 'UTC') AT TIME ZONE '<correct_time_zone>';
like image 57
Milen A. Radev Avatar answered Sep 28 '22 04:09

Milen A. Radev


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)
like image 43
Denis de Bernardy Avatar answered Sep 28 '22 04:09

Denis de Bernardy