0

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?

Colin
  • 3
  • 2
  • A timestamp does not store time zone information. Is the "range of records in BST" supposed to mean all timestamps after timestamp 2018-03-25 01:00 (the start of British Summer Time)? Please [edit] your question to make it clear. – Erwin Brandstetter Apr 13 '18 at 22:13

1 Answers1

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:

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'
...
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • yeah or use AT TIME ZONE 'europe/london' for date-aware conversion. – Jasen Apr 14 '18 at 00:40
  • @Jasen: While a time zone name (like 'Europe/London') is generally more reliable than any time zone abbreviation, it has no application in this particular operation, where the OP wants to convert BST to UTC. – Erwin Brandstetter Apr 14 '18 at 01:34
  • if he uses it OP uses it will result in no change to timestamps during the winter, only those during the summer, useful if the data spans several years, but not needed for this specific case. – Jasen Apr 14 '18 at 03:55