Should you store timestamps with or without a time zone?

Use timestamptz for anything that records when something happened. Both types occupy 8 bytes; timestamptz resolves its input to a UTC instant and renders it in the session's zone, while plain timestamp is a wall-clock reading with no instant attached. Keep plain timestamp only for wall-clock schedules that must not shift.

Key facts

| Fact | Value | |---|---| | Storage size of both types | 8 bytes, microsecond resolution, range 4713 BC to 294276 AD (Date/Time Types) | | What timestamptz stores | A UTC instant. The input zone is used for conversion and then discarded; no zone is stored (Date/Time Types) | | What controls input and output | The TimeZone session setting, defaulted from the server environment at initdb (Client Connection Defaults) | | now() | Start of the current transaction; constant for the whole transaction (Date/Time Functions) | | clock_timestamp() | Actual clock reading, changes within a single statement (Date/Time Functions) | | date_trunc(field, timestamptz, zone) | Three-argument form added in PostgreSQL 16 (Date/Time Functions) | | Cost of converting a column | ALTER TABLE ... ALTER COLUMN ... TYPE rewrites the table under ACCESS EXCLUSIVE (ALTER TABLE) |

Why this happens

The names mislead. timestamp with time zone sounds like a column that carries a zone alongside the value, and timestamp without time zone sounds like the neutral, portable choice. The reverse is closer to the truth. PostgreSQL's documentation is explicit: for timestamptz the value is converted to UTC using the appropriate offset and stored that way, and the original zone is not retained (Date/Time Types). Plain timestamp stores exactly the digits you gave it and nothing else, so two rows reading 2026-03-29 02:30:00 may be six hours apart and the database cannot tell.

The TimeZone setting does the work at both ends. On input, a literal with no offset is interpreted in the session zone; on output, the stored UTC instant is rendered back into it. That means the same row genuinely returns different text to two sessions, which is the behaviour people usually want and occasionally mistake for corruption. The setting is a normal client connection default and can be changed per session with SET TimeZone (Client Connection Defaults).

AT TIME ZONE is the conversion operator, and it runs in both directions depending on what you feed it (AT TIME ZONE):

-- timestamp -> timestamptz: "this wall clock, in that zone, is this instant"
SELECT timestamp '2026-03-29 01:30:00' AT TIME ZONE 'Europe/Dublin';

-- timestamptz -> timestamp: "that instant, shown as a wall clock in this zone"
SELECT now() AT TIME ZONE 'America/New_York';

Reading it as "the value on the left is being interpreted in, or rendered into, the zone on the right" removes most of the confusion.

Daylight saving is where plain timestamp stops being merely imprecise and becomes wrong. In Europe/Dublin on the 2026 spring transition, clocks jump from 01:00 to 02:00, so a wall-clock value of 01:30 on that date names an instant that never existed. On the autumn transition, 01:30 happens twice and names two instants an hour apart. PostgreSQL resolves these ambiguities for you when it converts, but only if the value is a timestamptz in the first place; a plain timestamp column preserves the ambiguity silently and hands it to your application. Version 17 added AT TIME ZONE variants that let you state which interpretation you want rather than accept the default (AT TIME ZONE).

Bucketing is the other common trap. date_trunc('day', created_at) on a timestamptz truncates in the session zone, so a report's day boundaries move with whoever runs it. Since PostgreSQL 16 the three-argument form pins the zone explicitly:

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

Finally, the three "current time" functions are not interchangeable. now() and transaction_timestamp() return the transaction's start time and do not move; statement_timestamp() returns the start of the current statement; clock_timestamp() reads the clock each time it is called, so it advances within a single statement (Date/Time Functions). A DEFAULT now() column is right for row creation stamps. Timing a long loop with now() measures nothing.

How to detect it

This lists every timestamp without time zone column outside the system catalogs, so you can decide which ones are deliberate:

SELECT table_schema, table_name, column_name, column_default, is_nullable
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, ordinal_position;

Expect a mixture. Columns named created_at, updated_at, deleted_at, last_seen_at or anything ending in _at are almost always event stamps and belong in timestamptz. Columns like scheduled_for, opens_at or billing_period_start may legitimately be wall-clock values. A column_default of now() on a plain timestamp column is a strong signal of an accident: now() returns a timestamptz, which is then cast down to the session zone and loses its instant.

To see which zone the session is actually using before you interpret any of it:

SHOW TimeZone;
SELECT current_setting('TimeZone'), now(), now() AT TIME ZONE 'UTC';

How MyDBA shows this

MyDBA's schema domain scores structural facts about your tables rather than opinions about naming: missing primary keys, foreign keys with no supporting index, NOT VALID constraints left unvalidated, and sequences approaching exhaustion. It is a useful reality check on how common schema drift is — across the 28 monitored instances currently scored in that domain, 57.1% fail the no-primary-key check and 67.9% the missing-foreign-key-index check, which suggests most schemas accumulate this kind of debt rather than being designed with it. Column types themselves are surfaced by the Schema Diff page, which compares two environments and reports differing column definitions, so a staging table that is timestamptz and a production table that is plain timestamp shows up as a named difference instead of a late-night surprise.

If you want to see the schema domain and the current findings against your own database, the free PostgreSQL health check runs the same scoring.

How to fix it

1. Decide per column, not per table. Run the detection query above and split the results into event stamps (convert) and wall-clock values (leave alone). Guessing in bulk is how a scheduling table ends up shifting by an hour.

2. Establish what the stored values actually mean. If your application wrote UTC into a plain timestamp column, the conversion clause is USING created_at AT TIME ZONE 'UTC'. If it wrote local time in a fixed zone, name that zone instead. Getting this wrong silently shifts every historical row, and no error is raised.

3. Convert the column. ALTER TABLE ... ALTER COLUMN ... TYPE with a USING clause rewrites the whole table and holds an ACCESS EXCLUSIVE lock for the duration, blocking reads as well as writes (ALTER TABLE):

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

On a large table, take a maintenance window, or use the add-column/backfill/swap approach: add a new timestamptz column, backfill in batches, keep both in sync with a trigger, then rename inside a short transaction.

4. Set a lock timeout first. SET lock_timeout = '5s'; before the ALTER means a failed attempt to acquire the lock fails fast instead of queueing behind a long query and blocking everything that arrives after it (Client Connection Defaults).

5. Rebuild dependent objects deliberately. A rewrite reindexes the table, but expression indexes, generated columns and views that referenced the old type need checking. A B-tree index on a timestamptz column works exactly as it did before; a functional index such as date_trunc('day', created_at) becomes non-immutable once the input is timestamptz, because the result depends on the session zone, and must be rewritten with the explicit three-argument form.

How to prevent it

Set the type in the migration that creates the column and treat plain timestamp as the exception that needs a comment explaining itself. Most ORMs default to whichever type their generic adapter picked years ago, so check what your framework emits rather than what you assume it emits.

Pin the server's TimeZone to UTC if your application does its own rendering, and make sure every environment agrees; a laptop on Europe/Dublin and a production server on UTC produce different query results from identical data, which is a difficult class of bug to reproduce. Partitioning and indexing need no special handling: timestamptz is a fixed-width 8-byte value, range partition bounds on it are unambiguous instants, and B-tree indexes and BRIN ranges behave exactly as they do for plain timestamp. Range bounds written as bare literals, on the other hand, are interpreted in the session zone at CREATE TABLE ... PARTITION OF time, so write them with an explicit offset.

Finally, add the detection query to whatever you already run against staging before a release. It costs nothing and it catches the new column before it has any rows to migrate. The same argument applies to the other structural decisions covered in five Postgres schema decisions you'll regret at 2am, the broader list in common Postgres schema mistakes, and the key-type question in UUID vs bigint primary keys.

FAQ

Does timestamptz store the time zone the value was written in?

No. This is the single most common misconception about the type. PostgreSQL converts the input to UTC using the offset implied by the input or the session's TimeZone, stores the UTC instant, and discards the zone (Date/Time Types). If you need to know the zone a user was in when they acted, store it in a separate text column.

Is timestamptz slower or larger than timestamp?

Neither. Both are 8 bytes with microsecond resolution and the same supported range (Date/Time Types). Comparison and indexing costs are effectively identical. The only extra work is the zone conversion on input and output, which is negligible next to the cost of getting the row off disk.

When is plain timestamp actually the right choice?

When the value is a wall-clock reading that must stay put regardless of zone or daylight saving: a recurring 09:00 local opening time, a calendar event that should still read 14:00 if the venue's zone rules change, a date-and-time field on a paper form being transcribed. In those cases the instant is derived later, usually by pairing the timestamp with a separate zone column.

What is the difference between now() and clock_timestamp()?

now() returns the current transaction's start time and stays constant for the whole transaction; clock_timestamp() reads the clock on each call and advances even within one statement (Date/Time Functions). Use now() for row stamps that should be consistent across a multi-statement transaction, and clock_timestamp() when you are measuring elapsed time.

Why does the same row show a different time to two colleagues?

Because timestamptz output is rendered in each session's TimeZone, and their clients are configured differently. The stored instant is identical. Run SHOW TimeZone; in both sessions to confirm, then either standardise the setting or render in the application layer.

Part of the The PostgreSQL health check checklist guide. Last verified against PostgreSQL 18, 2026-09-17.