PostgreSQL timestamp vs timestamptz: Which to Use and How to Find the Wrong Ones

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: Use timestamptz for anything that records when something happened, and timestamp only for a wall-clock time meant to read the same in every zone. Both types occupy 8 bytes, so the correct one is free, but Rails and Prisma both default to the naive one, which is how a schema ends up with hundreds of columns nobody chose. A migration linter cannot see them because the statements that created them were merged years ago; Schemity's schema lint reads the whole open ERD instead and marks each field row in the margin, with the conversion routed through a migration SQL diff you review first. Use timestamptz for any column that records when something happened, and timestamp only for a wall-clock reading that is meant to mean the same thing in every zone. Both types take 8 bytes, so the correct one is free. The reason most schemas have the wrong one anyway is that nobody chose it: Rails' t.datetime and Prisma's DateTime both emit the naive type unless you go out of your way, and by the time it matters the migration that created the column was merged years ago. That is what makes this different from a normal type mistake. A varchar(20) that should have been text announces itself the first time a value does not fit. A timestamp column behaves perfectly for years, then produces an hour of wrong answers at a daylight saving boundary, or shifts an entire reporting dashboard the day someone changes the server's time zone. Nothing in the schema looks wrong at any point, because nothing in the schema is invalid. Should I use timestamp or timestamptz in PostgreSQL? PostgreSQL's own wiki answers this in a page called Don't Do This, where don't use timestamp (without time zone) sits alongside its advice against char(n) , money and serial . The description there is the clearest one available: timestamp stores "a date and time you give it", which the wiki compares to a picture of a calendar and a clock, while timestamptz "records a single moment in time" and does the right thing with arithmetic between values entered in different zones. The practical difference: timestamp (without time zone) | timestamptz (with time zone) | | |---|---|---| | What the value means | The reading on a clock, with no clock stated | An instant, the same one for every reader | | On write | Stored exactly as given | Converted to UTC using the session zone or the stated offset | | On read | Returned exactly as stored | Converted back to the reader's session zone | | Arithmetic across a DST boundary | Wrong by an hour, silently | Correct | | Two servers in different zones | Disagree about what the row says | Agree | | Storage | 8 bytes | 8 bytes | | Right for | A time that is local by definition | Everything that happened | The last row is the whole decision. created_at , deleted_at , published_at , last_seen_at , every audit column and every event time are instants, and they belong in timestamptz . A shop that opens at 09:00 in whatever zone it stands in, a birthday, a recurring alarm the user wants at 07:00 no matter which country they wake up in - those are wall-clock readings, and timestamp is genuinely the right type for them. The set of columns in the second category is much smaller than the number of timestamp columns in a typical database, which is the tell. What does timestamptz actually store? Not a time zone. This is worth stating plainly because the objection to using the type is usually built on the assumption that it does, and that the extra information costs something. The PostgreSQL date/time types documentation is explicit on both halves. On what is kept: an input string with an explicit zone "will be converted to UTC using the appropriate offset for that time zone", and "in either case, the value is stored internally as UTC, and the originally stated or assumed time zone is not retained." On what it costs: the types table gives timestamp [ (p) ] [ without time zone ] and timestamp [ (p) ] with time zone a storage size of 8 bytes each, both spanning 4713 BC to 294276 AD at 1 microsecond resolution. So timestamptz is not a richer type that remembers where a row came from. It is the same 8 bytes with a defined meaning attached, and a conversion applied at the edges. If you need to know that a booking was entered in Europe/Berlin - because a future rule about that booking depends on the zone rather than the instant - that is a second column holding the zone name, and it is a modelling decision rather than a type choice. Why did my ORM create timestamp without time zone? Because that is what it does by default, in the two frameworks most likely to have generated your schema. Rails translates t.datetime to timestamp without time zone on PostgreSQL. The datetime_type setting that lets you change it to :timestamptz arrived in Rails 7.0, more than six years after the behaviour was reported: the issue standard migrations will generate a "timestamp without timezone" field was opened on 4 August 2015, with the complaint that AT TIME ZONE arithmetic then quietly uses the database server's zone rather than the one intended. The default itself has not changed, so a Rails application generated today still produces naive columns unless somebody adds the initializer. Prisma does the same by a different route. Its schema reference maps the DateTime scalar to timestamp(3) on PostgreSQL, with @db.Timestamptz(x) available as a native type attribute you apply per field. The documented fix is to write @db.Timestamptz(6) on every date column you care about, and the recurring reports of time zone surprises are largely the gap between that annotation existing and anyone knowing to type it forty times. This is the same shape as the ORM quietly deciding how a status column is constrained: the framework picks a physical representation, the choice never surfaces in a code review because there is nothing to review, and the schema ends up expressing a decision nobody made. MySQL inverts the vocabulary, which is worth knowing if you move between engines. Its manual states that MySQL "converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval", and that this "does not occur for other types such as DATETIME ". So MySQL's TIMESTAMP behaves like PostgreSQL's timestamptz , MySQL's DATETIME is the naive one, and the zone-aware type there is the one carrying a range limit: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC, against DATETIME 's year 9999. How do you find the timestamp columns you already have? The advice to use timestamptz is easy to accept and useless on its own, because the columns that need fixing were created before you accepted it. The real question is which of the two hundred date columns in the schema are wrong, and that is a question about the schema as it stands rather than about anything you are writing today. A migration linter cannot answer it. Squawk carries a prefer-timestamptz rule, and it is a good rule, but it fires on the column being added in the file being checked - which is why checking migrations and checking the schema are two different jobs. A schema that already holds three hundred naive columns produces no findings at all, forever, because none of those statements are in front of it. Schemity checks the model instead. timestamp-not-timestamptz is one of the seventeen rules in schema lint, and it reports the same value reading as a different instant depending on the session time zone. It runs against the diagram you have open, on your machine, with no connection required - so a schema reverse engineered from the live database is checked in its entirety, every column at once, including the ones created in 2019. The finding lands in the margin beside the exact field row rather than in a list you have to translate back into the picture, which matters here more than for most rules: a table with created_at , updated_at and published_at where only one was fixed during some earlier cleanup shows you precisely which one is still naive. The type name is already on the canvas next to it, since entities render each field's type and default directly, so the before and after are both readable without opening anything. Two honest limits. The rule is PostgreSQL-only - it and fk-array-column are the two of the seventeen that are, and the others run on every engine Schemity connects to - so a MySQL diagram is not asked this question at all, and the DATETIME version of it is not a rule today. And the field type icons deliberately will not help you: types are grouped by what the value is rather than by what the engine calls it, so DATE and TIMESTAMPTZ share one calendar glyph. The icon tells you it is a time, the type text and the lint strip tell you which one. The rule sits in the group called Costs, next to money-as-float , and the grouping is the point. It is not a runtime failure like a foreign key cycle that no row can be inserted into. It works. It charges you an hour of wrong answers twice a year and a permanent inability to compare two rows written on different servers, which is the sort of bill that never arrives all at once. When you do fix one, the conversion goes through the normal path: change the type in the ERD, read the generated migration SQL diff, then apply it. That review is not ceremony for this particular change, because converting a naive column means stating what its values meant - USING created_at AT TIME ZONE 'UTC' shifts every row by a fixed offset if the assumption is wrong, and the diff is where you see the clause before it runs rather than after. If the column is deliberately naive, ignore the finding once. The ignore is stored in the diagram's JSON file and travels with it, so a store-opening-hours column reads as a decision the team made rather than a note each person re-dismisses. Getting the next hundred columns right for free The columns that do