I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.
Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP WITHOUT TIME ZONE
, and now I'm paying for it with annoying time-zone related bugs. I want everything to be explicit, so want to convert the field to TIMESTAMP WITH TIME ZONE
. I realise that this doesn't store extra information, and all my timestamps are already in UTC, so the migration should be trivial, but I was wondering if there are any things complications / potential tripping blocks that will prove problematic (this is a production database with customers relying on it)?
Best Answer
Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC.
Check a possible column default for sanity, too. Any expression working with data type
timestamp
(likeLOCALTIMESTAMP
ornow()::timestamp
) is subject to the same problem. To change:Obviously, statements writing to the table also need to use
timestamptz
now - or you have another instance of the same problem with automatic conversion from the typetimestamp [without time zone]
.Since this is a production DB, best do it all in a single transaction to avoid race conditions - or even a single statement:
Basics: