Blog
3 min read

Postgres timestamp vs timestamptz: Which Should You Use?

timestamptz stores an absolute moment in time; timestamp stores a wall-clock reading with no zone. Why timestamptz is almost always right, what Postgres actually stores, how the session TimeZone changes what you see, AT TIME ZONE, date_trunc by local day, ORMs and drivers, and migrating.

Postgres has two timestamp types, and the names mislead almost everyone:

  • timestamptz (timestamp with time zone) — an absolute moment in time.
  • timestamp (timestamp without time zone) — a date and clock reading with no time zone attached.

The rule of thumb used by most Postgres experts: use timestamptz for anything that happened at a moment — created, updated, paid, logged in. (Dates and time zones in apps)

What timestamptz actually stores

Despite the name, timestamptz does not store a time zone. It stores a single instant (internally, microseconds since 2000-01-01 UTC). When you insert a value with an offset, Postgres converts it to that instant:

CREATE TABLE events (at timestamptz);
INSERT INTO events VALUES ('2026-10-02 09:00:00+02');   -- 07:00 UTC

When you read it, Postgres shows it in the session's TimeZone setting:

SET TIME ZONE 'UTC';             SELECT at FROM events;  -- 2026-10-02 07:00:00+00
SET TIME ZONE 'America/New_York'; SELECT at FROM events;  -- 2026-10-02 03:00:00-04

Same moment, displayed differently. Comparisons and sorting are always correct.

What timestamp stores

timestamp keeps exactly the digits you gave it and ignores any offset:

CREATE TABLE events2 (at timestamp);
INSERT INTO events2 VALUES ('2026-10-02 09:00:00+02');   -- stored as 09:00, +02 discarded

Was that 9 a.m. in Berlin? New York? UTC? The database doesn't know. If different servers or users insert in different zones, values become impossible to compare correctly — and bugs appear around daylight-saving changes.

Side by side

timestamptz timestamp
Represents An instant A wall-clock reading
Offset on input Used to convert Silently ignored
Output In session time zone As stored
Comparing events from different zones Correct Wrong unless everyone used UTC
Storage size 8 bytes 8 bytes

No cost difference — timestamptz is just safer.

When timestamp (without zone) is right

Rare cases where you mean a local reading, not an instant:

  • "The shop opens at 09:00" — every day, local time. (Often better as time plus the shop's time-zone name.)
  • A future appointment in a specific place, where you store the local time and the zone name ('Europe/Berlin'), because time-zone rules can change before the date arrives.
  • Birthdays → use date.

Practical patterns

Default and "now":

created_at timestamptz NOT NULL DEFAULT now()

Show times in a user's zone:

SELECT at AT TIME ZONE 'Europe/London' AS local_time FROM events;

AT TIME ZONE on a timestamptz returns a timestamp (local reading) in that zone.

Group by the user's local day, not UTC's:

SELECT date_trunc('day', created_at, 'Europe/Berlin') AS day, count(*)
FROM orders
GROUP BY day;

(The three-argument form needs Postgres 12+; otherwise date_trunc('day', created_at AT TIME ZONE 'Europe/Berlin').) Reports grouped by UTC day put late-evening orders on the wrong date for users far from UTC. (SQL GROUP BY)

Keep the server/session in UTC and convert at the edges — in the UI or with AT TIME ZONE.

In application code

  • JavaScript Date is an instant; drivers like node-postgres map timestamptz to a correct Date. With timestamp, the driver must guess a zone — usually the server's local zone — a classic source of off-by-hours bugs.
  • Send ISO strings with an offset (2026-10-02T07:00:00Z).
  • Prisma's DateTime maps to timestamp(3) by default; use @db.Timestamptz(3) to get timestamptz. Check your ORM's default. (What is an ORM?)

Migrating timestamp → timestamptz

If existing values were all written in UTC:

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

This rewrites the table and takes a lock; on large tables, plan it. (Migrations on large tables)

EasySpawn gives each app its own Postgres database, and Claude Code can audit your schema for timestamp columns that should be timestamptz and write the migration. See how it works or join the waitlist.

Related: Dates and Time Zones in Apps · What Is PostgreSQL? · Database Migrations Explained · Postgres Window Functions

Keep reading