Batch update timestamp records to correct timezone

Question

I'm looking to batch update some timestamp records in postgresql.

My app was storing timestamps to the timestamp column (without timezone) in the the wrong timezone (BST). I only noticed a while after DST kicked in (25th March) so not all the records are 'wrong'.

     timestamp       
-------------------
2018-04-13 00:02:01
2018-04-11 20:22:32
2018-04-10 12:00:56
2018-04-10 11:25:56
2018-04-10 01:57:05
2018-04-07 03:38:32
2018-04-05 12:25:10
2018-04-05 09:09:49

I want to convert the range of records in BST to UTC. What's the best way to go about doing this?

Answer 1

To adjust timestamp values for the difference between any two given time zones, use the AT TIME ZONE construct twice in a row. See:

• Ignoring time zones altogether in Rails and PostgreSQL

Assuming you want to adjust all timestamps after the begin of British Summer Time (BST) at '2018-03-25 01:00':

UPDATE tbl
SET    ts = ts AT TIME ZONE 'BST' AT TIME ZONE 'UTC'
WHERE  ts >= '2018-03-25 01:00';

Of course, in your particular example you might just subtract an hour:

...
SET    ts = ts - interval '1 hour'
...