Convert Postgres TIMESTAMP to TIMESTAMPTZ

Question

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)?

Answer 1

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.

ALTER TABLE tbl ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC';

Check a possible column default for sanity, too. Any expression working with data type timestamp (like LOCALTIMESTAMP or now()::timestamp) is subject to the same problem. To change:

ALTER TABLE tbl ALTER ts_column SET DEFAULT now();  -- or current_timestamp

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 type timestamp [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:

ALTER TABLE tbl
  ALTER ts_column TYPE timestamptz USING ts_column AT TIME ZONE 'UTC'
, ALTER ts_column SET DEFAULT now();

Basics:

• Ignoring timezones altogether in Rails and PostgreSQL

Answer 2

Would just like to add a cautionary note to Erwin Brandstetter's answer (couldn't comment because not enough reputation): whether SET DEFAULT now() is a good default really depends on the specific column.

For example, if your column is deleted_at, you would want to avoid the SET DEFAULT now().