35

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

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
GTF
  • 453
  • 1
  • 4
  • 6

2 Answers2

48

Data type and time zone

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

Works for any time zone. Unfortunately, it rewrites the whole table blocking every other access to the table for the time. For big tables, or under concurrent access, this can be an isssue. Since Postgres 12, there is a juicy exception for UTC timestamps. The release notes:

Allow ALTER TABLE ... SET DATA TYPE changing between timestamp and timestamptz to avoid a table rewrite when the session time zone is UTC (Noah Misch)

In the UTC time zone, these two data types are binary compatible.

So, do this instead:

SET timezone = 'UTC';  -- make sure the time zone is set properly
ALTER TABLE tbl ALTER ts_column TYPE timestamptz;
-- RESET timezone;     -- possibly

Related:

Column default

In addition, check a possible column default. Any expression working with data type timestamp (like LOCALTIMESTAMP or now()::timestamp) is subject to the same problem. To change (translating the given example LOCALTIMESTAMP):

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

See:

All at once

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

In a production DB, best do it all in a single transaction to avoid race conditions - or even in a single statement:

SET timezone = 'UTC';
ALTER TABLE tbl
  ALTER ts_column TYPE timestamptz
, ALTER ts_column SET DEFAULT now();

Basics:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
1

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().

Convexity
  • 11
  • 2