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.
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.
UPDATE <table>
SET <timestamptz_field> =
(<timestamptz_field> AT TIME ZONE 'UTC') AT TIME ZONE '<correct_time_zone>';
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With