Should I use timestamp or timestamptz in PostgreSQL?

1. Driver and session conversions. Many drivers convert your language's datetime to the session or JVM time zone before sending it. If one service runs in UTC and another in Europe/Berlin, they write different values for the same instant into a timestamp column, and you can't tell which rows are which.

2. Arithmetic across DST. With timestamp, '2026-03-08 01:30' + interval '1 hour' is just clock arithmetic, with no idea that 02:00–03:00 didn't exist that night in New York. With timestamptz, intervals are computed on the real timeline.

3. Comparisons with now(). now() returns timestamptz. Comparing it to a timestamp column forces an implicit conversion using the session's time zone, so the result of WHERE created_at > now() - interval '1 day' depends on who is connected.

The rule

Use timestamptz for anything that happened: created_at, paid_at, logged_in_at, expires_at.

It's what the PostgreSQL wiki's "Don't Do This" page recommends too. Keep the session TimeZone at UTC on servers so logs and ad-hoc queries are predictable, and convert to the user's zone at the edge:

SELECT created_at AT TIME ZONE 'America/Chicago' AS local_time
FROM orders;

When timestamp is right

Use timestamp (or date + time) for future local times whose meaning is tied to a place, not a moment:

• "The store opens at 09:00 local time."
• "The meeting is 2027-06-01 10:00 in Paris."

If Paris changes its DST rules before 2027, the meeting should still be at 10:00 on the wall clock. A stored UTC instant would drift. For these, store the local time and the IANA zone name next to it:

CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    starts_at  timestamp NOT NULL,   -- wall-clock time
    time_zone  text      NOT NULL    -- e.g. 'Europe/Paris'
);

SELECT starts_at AT TIME ZONE time_zone AS starts_at_instant FROM events;

Migrating an existing column

If a timestamp column really holds UTC values, you can convert it in place:

ALTER TABLE orders
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'UTC';

This rewrites the table and takes an ACCESS EXCLUSIVE lock. On large tables, plan a maintenance window or do it as add-column, backfill in batches, then swap. Check first that the values really are UTC; if different writers used different zones, fix the data before you trust the conversion.

Checklist

1. Events that happened: timestamptz.
2. Future local appointments: timestamp plus a zone name column.
3. Server and session TimeZone = UTC; convert for display in the app or with AT TIME ZONE.
4. Never rely on a timestamp column to remember an offset. It discards it.

What is the difference between PostgreSQL and MySQL for a web application?

Side-by-side SQL for the differences web developers hit daily: quoting, case sensitivity, identity columns, RETURNING, upserts, booleans, JSON, GROUP BY, full-text search, migrations and time zones.

#data-types#migrations#orm