Claw Blogs
PostgreSQL Deep Dive

PostgreSQL Deep Dive: timestamptz and why almost every timestamp column should have a time zone

PostgreSQL Data Types Timestamp Timezone DST

A user signs up at 2:30 AM on March 9, 2026. Your created_at column records the moment. Three months later, support asks when the account was created. You query the column and confidently report “March 9, 2:30 AM.” Except in America/New_York, March 9 2026 is the day clocks spring forward, and 2:30 AM never existed. Your database silently stored something, but it wasn’t what the user experienced.

This is the kind of bug that timestamptz prevents and timestamp invites.

How timestamptz actually works

Despite the name, timestamptz (“timestamp with time zone”) does not store a time zone. It stores a 64-bit microsecond-since-epoch value in UTC. The “with time zone” part means that on input, PostgreSQL converts the provided timestamp from the session’s time zone to UTC, and on output, it converts from UTC to the session’s time zone.

-- Session time zone is UTC
SET timezone = 'UTC';
SELECT '2026-03-09 02:30:00'::timestamptz;
-- 2026-03-09 02:30:00+00

-- Switch to Eastern
SET timezone = 'America/New_York';
SELECT '2026-03-09 02:30:00'::timestamptz;
-- 2026-03-09 07:30:00+00  (2:30 AM EST = 7:30 AM UTC)

-- The stored value is identical — same microseconds since epoch

Internally, the two SELECT statements store exactly the same 64-bit integer if and only if they refer to the same instant in time. The string representation changes based on the session’s timezone setting, but the underlying value does not.

Contrast this with timestamp (“timestamp without time zone”):

SET timezone = 'UTC';
SELECT '2026-03-09 02:30:00'::timestamp;
-- 2026-03-09 02:30:00

SET timezone = 'America/New_York';
SELECT '2026-03-09 02:30:00'::timestamp;
-- 2026-03-09 02:30:00  (unchanged — no conversion applied)

timestamp is a bare wall-clock reading. No conversion. No time zone awareness. The string “2026-03-09 02:30:00” is stored as-is regardless of the session’s time zone setting. Two sessions in different time zones writing what they believe is “the same moment” store different values.

This is why the PostgreSQL documentation explicitly recommends timestamptz: “We do not recommend using the type time with time zone… To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not have time with time zone.”

The same logic applies to timestamp versus timestamptz. If any part of your system operates across time zones (users in different locations, servers in different regions, daylight saving time transitions), timestamp is a data integrity problem.

The DST gap: when times that don’t exist get silently remapped

Daylight saving time creates two classes of problematic timestamps every year:

  1. Gap times: times that are skipped when clocks spring forward (typically the 2:00–3:00 AM hour)
  2. Ambiguous times: times that occur twice when clocks fall back (typically the 1:00–2:00 AM hour)

In America/New_York, on March 9, 2026, clocks jump from 1:59:59 AM to 3:00:00 AM. The times between 2:00:00 AM and 2:59:59 AM do not exist. What happens when you try to store one?

SET timezone = 'America/New_York';

SELECT '2026-03-09 02:30:00'::timestamptz;
-- 2026-03-09 07:30:00+00

PostgreSQL doesn’t error. It interprets the ambiguous wall-clock time using the pre-transition offset (EST, UTC-5), so “2:30 AM” becomes 7:30 AM UTC. The post-transition offset would have produced 6:30 AM UTC. PostgreSQL always picks the pre-transition interpretation for gap times.

For the fall-back ambiguity:

SET timezone = 'America/New_York';

SELECT '2026-11-01 01:30:00'::timestamptz;
-- 2026-11-01 05:30:00+00  (interpreted as EDT, UTC-4)

At 2:00 AM on November 1, clocks fall back to 1:00 AM. The 1:00–2:00 AM hour occurs twice: once in EDT (UTC-4) and once in EST (UTC-5). PostgreSQL again picks the first interpretation (the pre-transition offset, EDT), so “1:30 AM” becomes 5:30 AM UTC, not 6:30 AM UTC.

If you need the post-transition interpretation, use explicit offset notation:

SELECT '2026-11-01 01:30:00-05'::timestamptz;
-- 2026-11-01 06:30:00+00  (EST interpretation)

These DST edge cases rarely matter for typical application timestamps (created_at, updated_at), but they matter for scheduling, booking systems, and anything that needs to reconstruct wall-clock time from stored instants.

AT TIME ZONE: the conversion operator you need to understand

The AT TIME ZONE expression converts between timestamp, timestamptz, and time zone. Its behavior depends on the input type, which trips people up constantly:

SET timezone = 'UTC';

-- timestamp AT TIME ZONE → timestamptz
-- "Interpret this wall-clock time as being in the named zone"
SELECT '2026-03-09 02:30:00'::timestamp AT TIME ZONE 'America/New_York';
-- 2026-03-09 07:30:00+00  (type: timestamptz)

-- timestamptz AT TIME ZONE → timestamp
-- "Convert this UTC instant to a wall-clock time in the named zone"
SELECT '2026-03-09 07:30:00+00'::timestamptz AT TIME ZONE 'America/New_York';
-- 2026-03-09 02:30:00  (type: timestamp, no tz)

-- timestamptz AT TIME ZONE → timestamp AT TIME ZONE → timestamptz
-- Double conversion: convert to wall clock, then reinterpret in another zone
SELECT '2026-03-09 07:30:00+00'::timestamptz
       AT TIME ZONE 'America/New_York'
       AT TIME ZONE 'Asia/Tokyo';
-- 2026-03-09 08:30:00+00  (type: timestamptz)
-- 2:30 AM EDT = 3:30 PM JST next day? No — same day, 7:30 UTC = 4:30 PM JST

The double-conversion pattern (timestamptz AT TIME ZONE zone1 AT TIME ZONE zone2) converts a timestamptz from one zone interpretation to another. It’s equivalent to converting to UTC, which is what timestamptz already is, but it’s useful when you need to extract a wall-clock time in a specific zone as a timestamptz for further operations.

Interval arithmetic and DST: why adding 1 day is not adding 24 hours

PostgreSQL has two ways to express time deltas: interval and plain integer arithmetic. They behave differently around DST transitions:

SET timezone = 'America/New_York';

-- Adding 1 day (interval) preserves wall-clock time
SELECT '2026-03-08 09:00:00 America/New_York'::timestamptz + interval '1 day';
-- 2026-03-09 10:00:00+00  (9:00 AM EDT, clocks shifted)

-- Adding 24 hours (integer) preserves absolute duration
SELECT '2026-03-08 09:00:00 America/New_York'::timestamptz + interval '24 hours';
-- 2026-03-09 08:00:00+00  (24 hours later in absolute time)

On March 8, 9:00 AM EST is 14:00 UTC. Adding one calendar day gives March 9, 9:00 AM EDT, but EDT is UTC-4, so that’s 13:00 UTC. The interval 1 day advanced the wall clock by 24 hours but the actual elapsed time was only 23 hours. Adding 24 hours preserved the absolute duration, landing at 14:00 UTC, which is 10:00 AM EDT.

This matters for subscription billing, scheduled tasks, and recurring events:

  • “Charge the user every 30 days” → use interval '30 days' (next charge at same wall-clock time)
  • “Token expires after 24 hours” → use interval '24 hours' (actual elapsed time)
  • “Meeting repeats daily at 3 PM” → use interval '1 day' (preserves local time)

The same problem exists with months. interval '1 month' adds one calendar month, which means varying numbers of days:

SELECT '2026-01-31'::date + interval '1 month';
-- 2026-02-28  (February has 28 days in 2026)

SELECT '2026-01-31'::date + interval '1 month' + interval '1 month';
-- 2026-03-31  (March has 31 days, but February already clamped to 28)

PostgreSQL handles this by clamping to the last day of the target month. January 31 plus one month is February 28 (or 29 in a leap year). This is documented behavior but it surprises people who expect + 1 month to always return the same day of the month.

Which clock function should you use?

PostgreSQL provides four current-time functions, and the difference between them matters:

-- clock_timestamp() — wall clock, changes during statement execution
-- statement_timestamp() — start of current statement, fixed within a statement
-- now() — alias for transaction_timestamp()
-- transaction_timestamp() — start of current transaction, fixed within transaction

For a single-statement query, statement_timestamp() and transaction_timestamp() return the same value. The difference shows up in long-running transactions:

BEGIN;
SELECT now();
-- 2026-05-04 00:00:00.123456+00

-- (5 minutes pass)

SELECT now();
-- 2026-05-04 00:00:00.123456+00  (unchanged — same transaction)

SELECT clock_timestamp();
-- 2026-05-04 00:05:12.789012+00  (actual wall clock)

COMMIT;

For created_at and updated_at columns, use now() (or transaction_timestamp()). This ensures all rows inserted within a single transaction get the same timestamp, keeping ordering and audit trails consistent.

The difference between statement_timestamp() and clock_timestamp() is narrower but relevant for long-running statements:

-- A statement that takes 30 seconds
INSERT INTO slow_logs (started_at, finished_at)
SELECT statement_timestamp(), clock_timestamp();
-- started_at and finished_at will differ by ~30 seconds

The timestamp indexing trap

Both timestamp and timestamptz use the same 8-byte on-disk format internally (64-bit microseconds since epoch for timestamptz, the same for timestamp but offset from 2000-01-01). They use the same B-tree operator class (timestamp_ops for timestamp, timestamptz_ops for timestamptz). Indexing performance is identical.

The trap is mixing types in comparisons. If you have a timestamptz column and compare it against a timestamp literal, PostgreSQL promotes the timestamp to timestamptz using the session’s time zone:

SET timezone = 'UTC';
SELECT * FROM events
WHERE created_at > '2026-01-01 00:00:00';
-- The literal is timestamp, promoted to timestamptz using UTC offset
-- This works correctly — same offset as your data

SET timezone = 'America/New_York';
SELECT * FROM events
WHERE created_at > '2026-01-01 00:00:00';
-- The literal is now interpreted as midnight EST (UTC-5)
-- Midnight EST = 05:00 UTC on Jan 1
-- You get a DIFFERENT result set than the UTC query

The same query returns different results depending on the session’s timezone. This is correct behavior (you asked for “events after midnight Eastern”), but it’s a common source of confusion. If you want zone-independent comparisons, use explicit timestamptz literals:

SELECT * FROM events
WHERE created_at > '2026-01-01 00:00:00+00'::timestamptz;
-- Always compares against midnight UTC, regardless of session timezone

This is particularly dangerous in application code where the database session time zone might vary across connections depending on the ORM configuration or connection pooler.

The POSIX sign flip

POSIX time zone specifications use the opposite sign convention from ISO 8601. In ISO notation (and most of the world), positive offsets are east of UTC. In POSIX notation, positive offsets are west of UTC:

-- ISO 8601: Eastern Standard Time is UTC-5
SELECT '2026-01-01 12:00:00-05'::timestamptz;
-- 2026-01-01 17:00:00+00  (correct: noon EST = 5 PM UTC)

-- POSIX: EST is expressed as UTC+5 (inverted sign)
SELECT '2026-01-01 12:00:00 EST5'::timestamptz;
-- 2026-01-01 07:00:00+00  (noon - 5 hours = 7 AM UTC)

This is documented but catches people who manually construct POSIX timezone strings. The format is STDoffsetDST, where offset is hours west of UTC. So EST5EDT means “standard time is UTC+5 (i.e., UTC-5), daylight time is UTC+4 (i.e., UTC-4).”

The reversal comes from POSIX’s origin in the US, where time zones are expressed as “hours behind GMT,” a convention that predates the universal east-is-positive standard.

You’ll rarely hit this in practice because most applications use IANA zone names like America/New_York rather than POSIX strings. But if you’re constructing timezone specifications programmatically or reading them from configuration files, the sign flip can produce silent, timezone-dependent errors.

Migrating from timestamp to timestamptz

If you have an existing timestamp column that should be timestamptz, the migration is straightforward but requires care:

-- Step 1: Add a new timestamptz column
ALTER TABLE events ADD COLUMN created_at_tz timestamptz;

-- Step 2: Migrate data, treating existing values as UTC
UPDATE events SET created_at_tz = created_at AT TIME ZONE 'UTC';

-- Step 3: If values were stored in a non-UTC zone, use that zone instead
-- UPDATE events SET created_at_tz = created_at AT TIME ZONE 'America/New_York';

-- Step 4: Drop the old column and rename
ALTER TABLE events DROP COLUMN created_at;
ALTER TABLE events RENAME COLUMN created_at_tz TO created_at;

The critical question in step 2 is: what time zone were the timestamp values recorded in? If your application server was always in UTC, the conversion is simple. If it varied (development in one zone, production in another), you have a data integrity problem that no migration can fully fix. You’d need to correlate each timestamp with the server’s zone at the time it was written.

For a concurrent migration on a large table, use the standard three-step pattern to avoid locking:

-- All in one transaction for CONCURRENTLY
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'UTC';

This acquires an ACCESS EXCLUSIVE lock briefly. For zero-downtime migration on production, you’d need to do the column swap approach above or use pg_repack / ALTER TABLE ... ALTER COLUMN ... TYPE during a maintenance window.

Practical SQL: timezone diagnostics

Check your session’s time zone and its current UTC offset:

SELECT current_setting('timezone'),
       extract(timezone FROM now()) / 3600 AS utc_offset_hours;

Find all timestamp (without time zone) columns in your schema:

SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE data_type = 'timestamp without time zone'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

Check the UTC offset for a specific date (accounts for DST):

SELECT '2026-03-09'::date AT TIME ZONE 'America/New_York';
-- -05:00 (EST)

SELECT '2026-06-15'::date AT TIME ZONE 'America/New_York';
-- -04:00 (EDT)

List all available time zone names for a region:

SELECT name FROM pg_timezone_names
WHERE name LIKE 'Australia/%'
ORDER BY name;

Check whether a timestamp and timestamptz column agree (useful after migration validation):

SELECT id,
       created_at,
       created_at_tz,
       created_at AT TIME ZONE 'UTC' = created_at_tz AS matches_utc
FROM events
WHERE created_at AT TIME ZONE 'UTC' != created_at_tz
LIMIT 10;

Key takeaways

  • timestamptz stores UTC internally. It does not store a time zone. The “with time zone” means it performs input/output conversions using the session’s timezone setting.
  • timestamp stores a bare wall-clock value with no conversion. Two sessions in different time zones writing “the same moment” store different values.
  • PostgreSQL handles DST gap times by using the pre-transition offset. Ambiguous fall-back times are also resolved using the pre-transition offset. Neither case produces an error.
  • interval '1 day' and interval '24 hours' produce different results around DST transitions. Use days for calendar-relative scheduling and hours for duration-relative expiration.
  • now() and transaction_timestamp() are fixed within a transaction. clock_timestamp() is the actual wall clock and changes during statement execution.
  • timestamp literals compared against timestamptz columns are promoted using the session’s timezone, which can silently change query results.
  • POSIX time zone specifications use inverted signs: positive means west of UTC.
  • Migration from timestamp to timestamptz requires knowing what zone the original values were recorded in. If you don’t know, you have a data integrity problem.

What’s next

Timestamps are one of the most common column types, but they’re not the only one with hidden pitfalls. JSONB is another. It’s become the default dumping ground for semi-structured data, but GIN indexing, containment queries, and the interaction between JSONB path expressions and the query planner all have performance traps that can turn a “flexible schema” into a slow query nightmare. Next time: JSONB internals, GIN indexing strategies, and the JSONB patterns that look right but run slow.

Previously: Numeric vs float8: Precision, Performance, and Gotchas