Migrating timestamp to timestamptz Safely

The created_at columns across the schema were created as timestamp without time zone, and everyone “knows” the values are UTC. Then a service written in another language starts writing local times, reports disagree across time zones, and the team decides to move every timestamp column to timestamptz. The obvious statement, ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz, has two hidden properties. It can be either instant or a full table rewrite, depending on one session setting. And it reinterprets every stored value using the session’s TimeZone — so if that setting is wrong, every timestamp shifts by hours, silently and permanently. This guide shows how to make the conversion both instant and correct on PostgreSQL 12+, and how to verify it. It belongs to Changing Column Types Safely.

Symptom / Error Signatures

The warning signs are about correctness as much as locking:

• A test conversion on a copy shows pg_relation_filenode('events') changing — the table was rewritten — and the ALTER takes minutes.
• After conversion, timestamps are off by the server’s or client’s UTC offset: an event recorded at 2026-09-18 10:00 now reads 2026-09-18 08:00:00+00.
• Different migration environments produce different results, because CI runs in UTC while a developer laptop or a managed database defaults to a local zone.
• Application code comparing timestamps to “now” starts misbehaving around DST transitions.

Check the session setting that governs all of this:

-- PostgreSQL · read-only
SHOW timezone;                 -- the value the ALTER will use to interpret existing values
SELECT current_setting('server_version_num')::int >= 120000 AS pg12_or_later;

Root Cause Analysis

timestamp without time zone stores a wall-clock reading with no zone. timestamptz stores an absolute instant (internally UTC) and displays it in the session’s zone. Converting between them requires deciding which zone the wall-clock readings were in, and PostgreSQL uses the session’s TimeZone for that decision.

PostgreSQL 12 added an optimisation: when the session TimeZone is UTC (or an equivalent zero-offset zone), a wall-clock reading and the corresponding UTC instant have the same internal representation, so the conversion requires no change to stored data and no rewrite. With any other zone, each value must be shifted, so the table is rewritten and indexes rebuilt under ACCESS EXCLUSIVE. That makes the UTC session both the fast path and — provided the data really is UTC — the correct one.

The optimisation says nothing about whether your data is UTC. If some writers stored local times, converting in UTC preserves their mistake; fixing mixed data needs a separate, row-selective correction before or after the type change.

Immediate Mitigation

1. Establish what zone the data is in. Check the writers: application code, drivers and connection settings. A sanity check against another trusted source helps — for example, comparing created_at with an event’s timestamp in a log or an external system for a sample of rows.

2. Rehearse on a copy and confirm no rewrite.

-- PostgreSQL 12+ · staging copy · compares the storage file before and after
SELECT pg_relation_filenode('events') AS before;
SET timezone = 'UTC';
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz;
SELECT pg_relation_filenode('events') AS after;   -- must equal "before"

3. Run the conversion in production with the zone pinned in the same session. Put the SET in the migration itself so it cannot depend on client defaults.

-- PostgreSQL 12+ · migration role · metadata only when TimeZone is UTC
-- WARNING: the SET must be in the same session/transaction as the ALTER; otherwise values shift and the table is rewritten.
BEGIN;
SET LOCAL timezone = 'UTC';
SET LOCAL lock_timeout = '3s';
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz;
ALTER TABLE events ALTER COLUMN updated_at TYPE timestamptz;
COMMIT;
-- ROLLBACK PATH: BEGIN; SET LOCAL timezone = 'UTC'; ALTER TABLE events ALTER COLUMN created_at TYPE timestamp; COMMIT;

Indexes on the column remain valid: PostgreSQL does not need to rebuild them in the metadata-only case.

4. Check dependent objects. Views that expose the column, functions with timestamp parameters and expression indexes may need updating; a view whose column type changes must be recreated.

Permanent Fix / Long-Term Pattern

Standardise on timestamptz for every column that records an instant, and on UTC for every database session that writes them: set timezone = 'UTC' at the database or role level (ALTER DATABASE app SET timezone = 'UTC') and in every driver’s connection configuration, so application sessions and migrations agree. Keep timestamp without time zone only for genuinely zone-less values, such as a store’s opening time or a birthday.

Convert columns table by table using the pinned-zone migration, each with its own rehearsal on a production-sized copy. On PostgreSQL 11 and older, or where data needs correction during conversion, use the general procedure in converting a column type with a shadow column. Add a lint rule rejecting new timestamp columns without an explicit justification, per Migration Linting & Static Analysis. Application code that parses timestamps should be deployed and tested against timestamptz first — drivers often return zone-aware objects for timestamptz and naive objects for timestamp, which changes comparison behaviour in languages such as Python.

Partitioned tables need one extra thought. A type change on a partitioned parent applies to every partition, and with the zone pinned to UTC it stays metadata-only for each one on PostgreSQL 12+ — except for the partition key itself: PostgreSQL refuses to alter the type of a column used in the partition key (cannot alter column ... because it is part of the partition key). Converting a timestamp partition key means building a new partitioned table and moving data into it, the approach described in Partitioning Live Tables Without Downtime.

Verification Checklist

• The data’s zone was confirmed as UTC (or corrected) before conversion.
• A rehearsal on a copy showed an unchanged pg_relation_filenode, proving no rewrite.
• The production migration set timezone = 'UTC' in the same transaction as the ALTER.
• Sample rows compared before and after conversion show identical instants when displayed in UTC.
• Database and driver defaults are UTC so future writes agree with converted data.

Frequently Asked Questions

Why does the TimeZone setting affect a schema change? Converting timestamp to timestamptz means deciding which zone the existing wall-clock values were in, and PostgreSQL uses the session’s TimeZone. With UTC on PostgreSQL 12+, the stored representation does not change, so there is no rewrite.

Does timestamptz store the time zone? No. It stores an absolute instant (internally UTC) and converts it to the session’s zone for display. The zone used at insert time is not recorded.

Is the reverse conversion also instant? On PostgreSQL 12+ with the session in UTC, converting timestamptz back to timestamp is also metadata-only, which makes the rollback cheap as long as the zone is pinned the same way.

What about MySQL? MySQL’s TIMESTAMP converts to UTC for storage using the session time_zone, while DATETIME stores wall-clock values; converting between them is a type change that requires a table copy. Pin time_zone = '+00:00' for the migration session and use an online schema change tool for large tables.