Postgres timestamptz: What It Stores and When to Use It

TL;DR: Postgres timestamptz is the timestamp with time zone type: it stores a single instant as UTC and does not store any time zone. On the way out, PostgreSQL converts that instant to your session's TimeZone setting. Use it for anything that actually happened at a moment in time; keep plain timestamp for wall-clock values like "store opens at 09:00".

Postgres timestamptz is a date-and-time type that represents an absolute point in time. When you insert '2026-10-24 09:00:00+02', PostgreSQL converts it to UTC (07:00:00 UTC) and keeps only that 8-byte value; the +02 is gone. When you select it, the value is rendered in whatever TimeZone your session uses, so the same row prints as 03:00:00-04 in New York and 16:00:00+09 in Tokyo. The PostgreSQL manual states it directly: "All timezone-aware dates and times are stored internally in UTC" and are converted to the zone in the TimeZone parameter before being displayed. That is why timestamptz compares correctly across clients, servers and daylight saving changes, and why the PostgreSQL wiki's "Don't Do This" page tells you not to use timestamp without time zone for real events.

What is timestamptz in PostgreSQL?

timestamptz is PostgreSQL's accepted abbreviation for the SQL standard type timestamp with time zone. The abbreviation is a PostgreSQL extension; writing just timestamp means timestamp without time zone, as the SQL standard requires. The key facts from the PostgreSQL 18 Date/Time Types documentation:

Internally, both are a 64-bit integer counting microseconds from 2000-01-01. The difference is entirely in how values are interpreted on input and output. So timestamptz uses 8 bytes, the same as timestamp, and there is no storage or index-size reason to prefer the zoneless type. The type that costs more is timetz, at 12 bytes, because it really keeps the offset; the manual calls its usefulness "questionable".

Does timestamptz store the time zone?

No. timestamptz does not store the time zone or the offset you supplied. It converts the input to UTC and discards the zone. If you need to remember which zone a user was in (for example to show "booked at 9:00 your time" later), store that zone name in a separate text column such as 'Europe/Berlin'.

You can prove it in any psql session:

SET timezone = 'America/New_York';

SELECT timestamptz '2026-10-24 09:00:00+02' AS ts;
--            ts
-- ------------------------
--  2026-10-24 03:00:00-04

SET timezone = 'Asia/Tokyo';

SELECT timestamptz '2026-10-24 09:00:00+02' AS ts;
--            ts
-- ------------------------
--  2026-10-24 16:00:00+09

The +02 you typed never comes back. Both outputs are the same instant, 07:00 UTC, printed in two session zones.

How the session TimeZone changes what you see

The TimeZone setting controls two things for timestamptz:

1. Input without an offset. A literal like '2026-10-24 09:00' cast to timestamptz is assumed to be in the session zone, then converted to UTC.
2. Output. Every timestamptz is converted from UTC to the session zone when displayed, with the offset appended.

SET timezone = 'UTC';
SELECT '2026-10-24 09:00'::timestamptz;   -- 2026-10-24 09:00:00+00

SET timezone = 'America/Los_Angeles';
SELECT '2026-10-24 09:00'::timestamptz;   -- 2026-10-24 09:00:00-07
-- same text, different instant: this one is 16:00 UTC

The session zone comes from postgresql.conf, a role or database setting (ALTER ROLE app SET timezone = 'UTC'), the client's PGTZ environment variable, or an explicit SET timezone. If servers run in UTC, the behavior stays hidden until someone connects from a laptop set to local time.

Check yours with:

SHOW timezone;

A worked example with real numbers

A SaaS app records signups. One user signs up in Berlin at 23:30 local time on 2026-03-28 (UTC+1, before the EU daylight saving change on 2026-03-29). The app sends '2026-03-28 23:30:00+01'.

Now a daily signups report runs WHERE signed_up_at >= '2026-03-28' AND signed_up_at < '2026-03-29' in UTC. The timestamptz row lands on March 28 because it really happened at 22:30 UTC. The timestamp row also lands on March 28 here, but only by luck: it holds 23:30 Berlin wall time, and a user in Tokyo writing the same way at 08:00 local would be counted as an 08:00 event with no way to recover the real instant. With 1,000 signups a day spread across zones, those off-by-hours rows quietly move between days and your daily counts stop matching your payment provider's.

timestamp vs timestamptz: which should I use?

Use timestamptz for anything that happened at a real moment: created_at, updated_at, payments, logins, log lines, sensor readings, email send times. It costs the same 8 bytes and makes comparisons correct no matter which server, container or developer laptop wrote the row.

Use timestamp only for wall-clock values that are deliberately not tied to an instant:

• Opening hours: "every store opens at 09:00 local."
• Recurring schedules: "standup every Monday at 10:00" should not shift when daylight saving changes.
• Future events in a named zone: store starts_local timestamp plus zone text, then compute the instant at query time with starts_local AT TIME ZONE zone. If a government changes its daylight saving rules, your event still happens at the right local time.

The PostgreSQL wiki's "Don't Do This" page goes further: don't use timestamp without time zone, and specifically don't use it "to store UTC times", because the database cannot tell UTC values from local ones. The convention lives in your team's heads instead of in the schema. It also advises against timetz and against timestamptz(0), since precision 0 rounds rather than truncates and can produce a value up to half a second in the future.

How do I convert timestamp to timestamptz?

For a single value, use AT TIME ZONE with the zone the wall time was recorded in:

-- timestamp (wall time) -> timestamptz (instant)
SELECT timestamp '2026-10-24 09:00:00' AT TIME ZONE 'Europe/Berlin';
-- 2026-10-24 07:00:00+00   (shown in a UTC session)

-- timestamptz (instant) -> timestamp (wall time in a zone)
SELECT timestamptz '2026-10-24 07:00:00+00' AT TIME ZONE 'Asia/Kolkata';
-- 2026-10-24 12:30:00

The direction flips with the input type: on a timestamp it returns timestamptz, on a timestamptz it returns timestamp (Date/Time Functions). Apply it once per value. Writing col AT TIME ZONE 'X' AT TIME ZONE 'X' almost always means one of them is wrong.

For a whole column, the recipe depends on what zone your existing values are in.

Case 1: the column already holds UTC wall times. Since PostgreSQL 12, changing the type between timestamp and timestamptz skips the table rewrite when the session time zone is UTC, because the two types are binary compatible in UTC (PostgreSQL 12 release notes). On a large table this turns a long exclusive-lock rewrite into a near-instant catalog change:

BEGIN;
SET LOCAL timezone = 'UTC';
ALTER TABLE events
  ALTER COLUMN created_at TYPE timestamptz;
COMMIT;

The SET LOCAL matters. Without it, PostgreSQL interprets every value in your session zone and rewrites the table, and if your session is not UTC, every row shifts by your offset.

Case 2: the column holds local wall times in a known zone. Tell PostgreSQL which zone with USING. This rewrites the table, so run it in a maintenance window or on a copy:

ALTER TABLE bookings
  ALTER COLUMN booked_at TYPE timestamptz
  USING booked_at AT TIME ZONE 'America/Chicago';

Verify afterwards by comparing a few known rows against your application logs or payment provider timestamps, with SET timezone = 'UTC' so the output is unambiguous.

Current time functions return timestamptz

now(), CURRENT_TIMESTAMP, statement_timestamp() and clock_timestamp() all return timestamptz. now() and CURRENT_TIMESTAMP are frozen at the start of the transaction, statement_timestamp() at the start of the statement, and clock_timestamp() keeps moving. For a created_at column, timestamptz NOT NULL DEFAULT now() is the standard pattern.

Common pitfalls

• Expecting your offset back. It is not stored. Store the zone name separately if you need it.
• Typed literals. TIMESTAMP '2026-10-24 10:00+02' is a timestamp, and the +02 is silently dropped. Write TIMESTAMPTZ '...' or TIMESTAMP WITH TIME ZONE '...'.
• Grouping by day in the wrong zone. date_trunc('day', created_at) uses the session zone. For a report in Indian time, use date_trunc('day', created_at, 'Asia/Kolkata') (PostgreSQL 12+) or set the session zone explicitly.
• Abbreviations. 'IST' or 'CST' are fixed offsets and ambiguous; use IANA names like 'Asia/Kolkata' so daylight saving rules apply.
• BETWEEN on timestamps. Use >= start AND < end; BETWEEN includes the end instant and double-counts midnight rows.

For more date work, see our guides to PostgreSQL DATEDIFF, adding intervals like DATEADD, GETDATE equivalents and CAST and type conversion.

Time zone bugs usually surface in reports: daily signups that never match Stripe, or a dashboard that changes when someone in another office opens it. If you would rather ask "signups per day in IST for the last 30 days" and get the answer with the exact SQL it ran, try AI for Database free at aifordatabase.com; it connects to Postgres read-only and comes with sample data.