PostgreSQL timestamp vs timestamptz: Which to Use
PostgreSQL timestamp vs timestamptz is the choice that silently corrupts time data. Learn the real difference, storage myths, AT TIME ZONE, and how to migrate safely.
The PostgreSQL timestamp vs timestamptz decision looks innocent — two types that both "store a date and time" — but pick the wrong one and your app silently records the wrong moment. A user in Karachi books a slot at 10:00, your server stores it, and a user in London sees 10:00 too — except those are two different instants, and now your scheduling logic is broken with no error anywhere. This guide shows you exactly what each type stores, why timestamptz is almost always the right default, and how to fix a table that's already using the wrong one. What you'll learn What timestamp and timestamptz actually store (the name timestamptz lies a little) A live demo of the silent-corruption bug, and why timestamp causes it The storage myth: both types are 8 bytes, and timestamptz stores no zone at all AT TIME ZONE : interpreting naive times vs rendering instants, with examples How to convert a timestamp column to timestamptz without losing data Session timezone settings that prevent an entire class of bugs Step 1 — See the bug: timestamptz vs timestamp in action Run this setup. The app records "10:00" wall-clock time — written once, then read back under two different session timezones: CREATE TABLE events ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, happened_at TIMESTAMP, -- timestamp WITHOUT time zone happened_at_tz TIMESTAMPTZ -- timestamp WITH time zone ); -- The write happens in Karachi local time: SET TIME ZONE 'Asia/Karachi'; INSERT INTO events (name, happened_at, happened_at_tz) VALUES ('Deploy finished', '2026-10-01 10:00:00', '2026-10-01 10:00:00+05'); -- Read as if your app server runs in UTC: SET TIME ZONE 'UTC'; SELECT name, happened_at, happened_at_tz FROM events; -- Now read as if you're querying from…