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 offset you give it. That one fact produces four classic bugs: a server TimeZone change that shifts readings, DST gaps and overlaps, now() compared against the wrong type, and AT TIME ZONE applied twice. Below: what each type stores, a behaviour table, the bugs with runnable SQL, ORM defaults as of October 2026, the one exception, and a migration recipe.
TL;DR
The SQL standard name for plain
timestampis "timestamp without time zone". That name is the footgun: it stores no zone and ignores one in the input[1].timestamptzdoes not store a zone either. It converts to UTC on input and back to the sessionTimeZoneon output[1].Prisma, Drizzle and Rails default to the zone-less type. Django's PostgreSQL backend uses the zone-aware one.
The one exception: a future local-time appointment ("9:00 in Kolkata on 3 March") should keep the local time plus an IANA zone name.
Migrating: on PostgreSQL 12 and later, an in-place type change can skip the table rewrite when the session zone is UTC[5]. Otherwise use the add-column, backfill, swap route.
What each type actually stores
The name timestamp with time zone misleads people into thinking the zone is saved. It is not. The docs say an input with an explicit zone is converted to UTC, an input without one is assumed to be in the session TimeZone and then converted, and in either case the value is stored as UTC and the original zone is not retained[1]. On output the value is converted from UTC to the current TimeZone setting[1]. So timestamptz is a point on the UTC timeline, rendered through whatever zone the session has.
Plain timestamp is a date and a clock reading, nothing else. Postgres never inspects a string before choosing its type, so TIMESTAMP '2004-10-19 10:23:54+02' is zone-less and the docs say Postgres "will silently ignore any time zone indication"[1]. Write TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' if you mean it. Bare timestamp means the zone-less type per the SQL standard[1], which is the wrong default for most applications.
Behaviour comparison: timestamp vs timestamptz
| Behaviour | timestamp | timestamptz |
|---|
| Storage and resolution | 8 bytes, 1 microsecond | 8 bytes, 1 microsecond |
| What is stored | Date and clock fields as written | A UTC instant |
| Offset in the input string (+02) | Silently ignored | Used to convert to UTC |
| Output | Same fields every time, whatever the session zone | Converted to the session TimeZone |
| AT TIME ZONE 'X' | Reads the value as local time in X, returns timestamptz | Returns timestamp: the clock in X at that instant |
| date_trunc('day', v) | Taken at face value; no zone argument allowed | Midnight in the session zone, or in a zone you pass as the third argument |
| EXTRACT(epoch FROM v) | Nominal seconds, treated as if the value were UTC | True seconds since 1970-01-01 00:00:00 UTC |
| generate_series with a day step | Fixed arithmetic on the clock fields | DST adjustments computed in the session zone or an optional zone argument |
| Can store a nonexistent or ambiguous local time | Yes, and cannot tell which instant was meant | No: it is already an instant |
The four classic bugs, with SQL
Bug 1: the server TimeZone changes and every reading moves
A session zone change makes timestamptz display differently but leaves the instants alone. For a timestamp column fed by DEFAULT now() it is corruption: now() returns timestamp with time zone[2], so the zone-less column records a clock reading in whatever zone the writing session had, and rows from before and after the change sit on different baselines. The docs example of the flattening operation, with the session zone at America/Los_Angeles[2]:
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05'
AT TIME ZONE 'America/Denver';
-- 2001-02-16 18:38:40 (a timestamp: the clock in Denver at that instant)
Bug 2: DST gaps and overlaps
A zone-less column can hold times that never happened or happened twice. In America/New_York clocks jump from 02:00 to 03:00 on 8 March 2026, so 02:30 does not exist, and on 1 November 2026 01:30 occurs twice. A timestamp cannot say which; a timestamptz is already an instant.
Arithmetic carries the same trap. The docs show a day and 24 hours giving different answers across a DST change[1]:
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
-- 2005-04-03 12:00:00-06
SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
-- 2005-04-03 13:00:00-06
Bug 3: comparing now() with a timestamp column
A filter like WHERE expires_at > now() compares two instants when expires_at is timestamptz. On a timestamp column Postgres converts one side using the session zone, so with UTC wall-clock values and an Asia/Kolkata session the comparison is off by 5 hours 30 minutes. Fix the type. Until then compare against now() AT TIME ZONE 'UTC'[2].
Bug 4: AT TIME ZONE applied twice
The operator flips direction depending on the input type, and it chains happily. The docs example[2]:
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo'
AT TIME ZONE 'America/Chicago';
-- 2001-02-16 05:38:40
Now the mistake. On a timestamptz column, created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata' first yields a UTC clock reading, then reads that as Kolkata local time and returns an instant 5 hours 30 minutes earlier than the original. To show a Kolkata clock reading, apply it once:
SELECT created_at AT TIME ZONE 'Asia/Kolkata' AS created_ist
FROM events;
date_trunc, generate_series, epoch and casts to date
date_trunc('day', ...) on a timestamptz truncates in a particular zone: the session TimeZone by default, or a zone you pass. On a timestamp the value is taken at face value and a zone argument is not allowed[2]. Daily reports should name the zone:
SELECT date_trunc('day', created_at, 'Asia/Kolkata') AS day_ist,
count(*)
FROM events
GROUP BY 1
ORDER BY 1;
EXTRACT(epoch ...) shows the gap in one number: the docs example gives 982384720.12 for a zone-aware literal and 982355920.12 for the zone-less one, exactly 8 hours (28,800 seconds) apart, because a timestamp is treated as if it were UTC[2]. Check values like these with the Unix Timestamp Converter.
generate_series on timestamptz computes DST adjustments in the zone you name, or the session zone if you omit it[6]. Name it and a daily series stays on local midnight:
SELECT * FROM generate_series(
'2001-10-22 00:00 -04:00'::timestamptz,
'2001-11-01 00:00 -05:00'::timestamptz,
'1 day'::interval,
'America/New_York');
Casts like created_at::date depend on the session zone, so write (created_at AT TIME ZONE 'Asia/Kolkata')::date when you want a specific local date.
ORM and driver defaults in October 2026
Defaults are split, and the split is where production mistakes start.
| Tool | Default column type | How to get timestamptz |
|---|
| Prisma | DateTime maps to timestamp(3)[7] | @db.Timestamptz(3) native type attribute[7] |
| Drizzle | timestamp() generates timestamp without zone[8] | timestamp({ withTimezone: true })[8] |
| Django | The PostgreSQL backend stores datetimes as timestamp with time zone, and time zone support is on by default in new projects[9] | Keep USE_TZ = True |
| Rails | "By default and for legacy reasons" timestamp without time zone[10] | Set datetime_type = :timestamptz in an on_load(:active_record_postgresqladapter) hook, before generating new migrations[10] |
| node-postgres | Parses timestamp as local time of the Node process (process.env.TZ)[11] | Use timestamptz columns; Date objects round-trip as instants |
The Prisma fix is one attribute, @db.Timestamptz(3); the docs page lists the mapping but no standalone example, so check it on your version. In Drizzle: timestamp('created_at', { withTimezone: true }).defaultNow(). The node-postgres default bites on servers whose TZ is not UTC: a zone-less value written as UTC is parsed as local time, so a Node process in Mumbai reads every old timestamp row 5 hours 30 minutes wrong.
The rule, and the one exception
The rule: every column that records when something happened (created, paid, logged in, sent, expired) is timestamptz. Write instants in, read instants out, and convert to a user's zone in the UI or in a query with an explicit AT TIME ZONE. Django's docs agree: store in UTC, use local time only with users[9]. Set the connection zone to UTC in your pool config.
The exception: a future appointment tied to local clocks. "The 9:00 stand-up in Kolkata next March" must keep meaning 9:00 there even if rules change. The docs note that for future times Postgres assumes the latest known zone rules continue indefinitely[1]. If a government changes DST later, an instant you computed today does not move with it, but the person still expects 9:00 on the wall. So store the local reading plus the IANA name, and compute the instant when you need it:
CREATE TABLE appointments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
local_start timestamp NOT NULL, -- 2027-03-03 09:00, wall clock on purpose
tz_name text NOT NULL -- 'Asia/Kolkata'
);
Validate zone names in the application, or with a trigger that looks them up in pg_timezone_names, since a check constraint cannot contain a subquery. Compute the instant with local_start AT TIME ZONE tz_name. Here timestamp is the right type, because the whole point is a clock reading with a separate zone. If you schedule jobs from such rows, for example on a cron-driven agent like the one in this Cloudflare free-tier build, compute the next UTC instant and store that too, then let the scheduler compare instants only.
Postgres 18 and time zones
PostgreSQL 18 shipped on 25 September 2025 and its release notes change nothing about how timestamp or timestamptz store or convert values. One neighbouring item matters: the session's time zone abbreviations are now checked before the timezone_abbreviations server variable[4]. Use full IANA names, since an abbreviation is only a fixed offset with no DST rules[1].
Migrating timestamp to timestamptz without downtime
First decide what the old values mean: a column written by DEFAULT now() under a UTC session holds UTC wall time, one written by an app in Mumbai holds IST. Use the zone the data was written in. Rows from a DST overlap cannot be repaired, because the information was never stored.
Route A: in-place type change
An ALTER COLUMN ... TYPE takes an ACCESS EXCLUSIVE lock unless the docs say otherwise, and normally rewrites the table and its indexes, needing up to twice the disk space[3]. The exception is a type change with no content change on a binary-coercible pair. PostgreSQL 12 added one for this exact pair: changing between timestamp and timestamptz avoids a table rewrite when the session zone is UTC, because the two types are binary compatible in UTC[5]. Indexes still rebuild unless the system can verify the new index is logically equivalent[3].
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL TimeZone = 'UTC';
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz;
COMMIT;
ANALYZE events;
Two cautions. I omitted USING on purpose: the release note describes the plain type change, and I have not confirmed that USING created_at AT TIME ZONE 'UTC' keeps the no-rewrite path. Test it on a copy and compare pg_relation_filenode('events') before and after; a changed number means a rewrite happened. And the ALTER docs say a type change removes column statistics, so run ANALYZE afterwards[3]. The lock queues behind long transactions, so keep lock_timeout short and retry.
Route B: new column, backfill, swap
Use this when the data is not UTC, the table is huge, or even a short exclusive lock is unacceptable.
-- 1. new column
ALTER TABLE events ADD COLUMN created_at_tz timestamptz;
-- 2. keep new writes in sync
CREATE FUNCTION events_sync_created_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.created_at_tz := NEW.created_at AT TIME ZONE 'UTC';
RETURN NEW;
END $$;
CREATE TRIGGER events_sync_created_at
BEFORE INSERT OR UPDATE OF created_at ON events
FOR EACH ROW EXECUTE FUNCTION events_sync_created_at();
-- 3. backfill in batches (repeat with the next id range until done)
UPDATE events
SET created_at_tz = created_at AT TIME ZONE 'UTC'
WHERE id >= 1 AND id < 50001 AND created_at_tz IS NULL;
-- 4. index the new column without blocking writes
CREATE INDEX CONCURRENTLY events_created_at_tz_idx ON events (created_at_tz);
Replace 'UTC' with the real source zone if the old values were local. The expression follows the docs: a zone-less value AT TIME ZONE a zone yields a timestamptz assuming the value was in that zone[2]. Keep batches small and pause if replication lag climbs. Once the null count reaches zero and the app reads the new column, swap in one short transaction:
BEGIN;
SET LOCAL lock_timeout = '3s';
DROP TRIGGER events_sync_created_at ON events;
ALTER TABLE events RENAME COLUMN created_at TO created_at_old;
ALTER TABLE events RENAME COLUMN created_at_tz TO created_at;
COMMIT;
ALTER INDEX events_created_at_tz_idx RENAME TO events_created_at_idx;
-- after a release or two with no readers of created_at_old:
ALTER TABLE events DROP COLUMN created_at_old;
Drop the trigger inside the same transaction as the rename, or it will start writing to a column that no longer means what it did. If the column had a default, the ALTER docs warn that a USING expression is not applied to defaults, so drop the default, change the type, and set a new one[3]. For Route B, set DEFAULT now() on the new column before the swap. Tidy long migrations with the SQL Formatter before review.
Quick answers
Does timestamptz store the time zone?
No. It stores a UTC instant. Input with a zone is converted to UTC, input without one is assumed to be in the session TimeZone, and the original zone is discarded. On output the value is converted into the current session zone, so what you see depends on the connection.
Is timestamptz bigger or slower than timestamp?
No. Both are 8 bytes with 1 microsecond resolution and the same range of 4713 BC to 294276 AD. Storage and range give you no reason to pick the zone-less type.
Why did my timestamp column ignore the +02 offset?
Because for a zone-less value Postgres silently ignores any time zone indication in the input. It reads the date and clock fields and drops the rest. Cast the literal as timestamptz, or change the column type, if the offset should count.
Can I change timestamp to timestamptz without rewriting the table?
On PostgreSQL 12 and later, yes in the narrow case where the session time zone is UTC and you change the type without altering the contents. Indexes may still rebuild, and the lock is still ACCESS EXCLUSIVE. For non-UTC data or very large tables, use the new-column route instead.
When should I keep timestamp instead of timestamptz?
For future local-time appointments, store the wall-clock timestamp plus an IANA zone name such as Asia/Kolkata, and derive the instant when needed. For anything that records when an event actually happened, use timestamptz.
Sources
PostgreSQL 12 release notes, E.23.3.1.4 General Performance (accessed 16 October 2026)
Prisma schema reference: PostgreSQL native type mapping for DateTime (accessed 16 October 2026)
Drizzle ORM: PostgreSQL column types, timestamp (accessed 16 October 2026)
Rails Guides: Active Record and PostgreSQL, datetime types (accessed 16 October 2026)
Every product mentioned is available at wowhow.cloud — pay once, ship forever.
Originally published at wowhow.cloud
Top comments (0)