Postgres timestamp vs timestamptz 2026: Rule and Migration
Use timestamptz for every column that records a moment in time, store it as UTC, and convert to a local zone only when you display it. Both types take 8 bytes and have 1 microsecond resolution[1], so the choice costs nothing in space. What differs is meaning: timestamptz stores an instant, while timestamp stores a wall-clock reading with no zone attached, and Postgres will silently drop any…
PostgreSQL offers two timestamp types: timestamp and timestamptz. The key difference lies in how they handle time zones. timestamptz stores an instant in UTC, while timestamp stores a wall-clock reading without any zone. Regardless of the input, both types are stored as UTC, and Postgres converts them to the session's local time zone when displaying.
Choosing the right timestamp type is crucial to avoid common bugs. The SQL standard refers to plain timestamp as "timestamp without time zone" because it lacks a time zone component. However, many developers mistakenly use it as the default due to its name.
Using timestamptz for every column that records a moment in time is recommended. Store it as UTC and convert to a local zone only when displaying. This approach eliminates the four classic bugs associated with timestamp columns: server TimeZone changes, DST gaps and overlaps, comparing now() against the wrong type, and applying AT TIME ZONE twice.
Prisma, Drizzle, and Rails default to the zone-less type, whereas Django's PostgreSQL backend uses the zone-aware type. The one exception is when dealing with a future local-time appointment, such as a 9:00 meeting in Kolkata on March 3rd. In this case, the local time should be kept along with an IANA zone name.
To migrate from zone-less timestamp columns to timestamptz, use an in-place type change if the session zone is UTC. For other cases, follow the add-column, backfill, and swap route. By adopting timestamptz and following the migration recipe, you can ensure accurate and reliable timestamp handling in your PostgreSQL database.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.