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'.

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?

  • 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. Apr 13, 2018 at 22:13

1 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:

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

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'
  • yeah or use AT TIME ZONE 'europe/london' for date-aware conversion.
    – Jasen
    Apr 14, 2018 at 0: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. Apr 14, 2018 at 1: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, 2018 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.