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.

What does the bug look like?

It takes one row to see it. I ran this on PostgreSQL 18.3: one table, one INSERT of now() into both types from a session in New York, then the same row read back from a session in Tokyo.

CREATE TABLE events (naive timestamp, aware timestamptz);

SET TimeZone = 'America/New_York';
INSERT INTO events VALUES (now(), now());

SET TimeZone = 'Asia/Tokyo';
SELECT naive, aware FROM events;

           naive            |             aware
----------------------------+-------------------------------
 2026-10-20 04:00:38.627389 | 2026-10-20 17:00:38.627389+09

Read from New York, both columns said 04:00. Read from Tokyo, the timestamptz column moved to 17:00+09, which is the same instant on a Tokyo clock. The timestamp column still says 04:00, and nothing in it says that this was a New York clock. A report server in another zone, a worker container left on UTC, or a developer laptop in Tokyo all read it as their own 04:00, 13 hours away from when the row was written.

Daylight saving does the same thing without a second server. In New York, clocks jumped from 02:00 to 03:00 on 8 March 2026:

SET TimeZone = 'America/New_York';
SELECT timestamp   '2026-03-08 03:30' - timestamp   '2026-03-08 01:30' AS naive_elapsed,
       timestamptz '2026-03-08 03:30' - timestamptz '2026-03-08 01:30' AS aware_elapsed;

 naive_elapsed | aware_elapsed
---------------+---------------
 02:00:00      | 01:00:00

One real hour passed. The naive type says two, because it does not know the hour between them never existed. Adding interval '1 hour' to 01:30 gives 02:30 as a timestamp, a time no clock in New York showed that night, and 03:30-04 as a timestamptz. Any job duration, session length or SLA timer that spans the switch is off by an hour, twice a year.

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:

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.

The catalog can answer it, and this query is the free version. It lists every naive column in the database, outside PostgreSQL’s own schemas:

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 1, 2, 3;

Run it once and you have the list. What it does not give you is the context to decide each row: which table the column sits on, what the table’s other date columns are, and whether a created_at beside a timestamptz updated_at was a decision or a half-finished cleanup.

Schemity checks the model instead. timestamp-not-timestamptz is one of the forty-one 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 runs on PostgreSQL and CockroachDB only - most of the forty-one run on several engines, and each database has a few of its own - 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, which catches prices stored as floating point, 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.

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.

Does converting timestamp to timestamptz rewrite the table?

Not if the session time zone is UTC, and that is the fact that changes how you write the migration. Since PostgreSQL 12, the release notes say, ALTER TABLE ... SET DATA TYPE between the two types skips the rewrite in a UTC session, because “in the UTC time zone, these two data types are binary compatible”. I measured it on PostgreSQL 18.3 with a million rows:

The third row is the version most guides give. It means the right thing and costs a full rewrite under an ACCESS EXCLUSIVE lock, because PostgreSQL cannot tell that the expression is a no-op. The first row produces the same values (I compared both on the same data) and only touches the catalog. Indexes on the column are still rebuilt, so a large index is the remaining cost.

The second row is the trap. Without a USING clause, or with a plain created_at::timestamptz, PostgreSQL reads each stored value as a clock reading in the session’s zone. If your values were written in UTC and the session is in New York, every row moves by four or five hours, and the conversion succeeds without a warning. So the safe migration is two lines:

SET TimeZone = 'UTC';
ALTER TABLE orders ALTER COLUMN created_at TYPE timestamptz;

Change 'UTC' only if the values really were written in another zone, and accept the rewrite that comes with it. Check one row whose real time you know before and after.

When you fix one in Schemity, the conversion goes through the normal path: change the type in the ERD, read the generated migration SQL diff, then apply it. The diff opens with SET TimeZone = 'UTC'; before the ALTER COLUMN, so the values are read as UTC whichever zone the server defaults to, and the impact list does not count a rewrite unless the fractional digits shrink or the column is an array. A migration file you check with Schemity is held to the same rule: the conversion counts as a rewrite unless the file itself sets the session to UTC first. That review is where you confirm the values really were written in UTC before the statement runs.

Getting the next hundred columns right for free

The columns that do not exist yet are the cheap half of this problem, and the fix is not vigilance.

Every new entity in Schemity starts from the active template, so a baseline of id, created_at TIMESTAMPTZ and updated_at TIMESTAMPTZ means the convention is what happens when you do nothing - and it is the same place to settle whether that id is a bigint or a UUID, which is the other type decision a generator makes on your behalf, rather than something enforced afterwards by a linter or a reviewer. That is the same reasoning behind fixing the ORM default: an initializer setting datetime_type = :timestamptz, or a Prisma annotation on the field, moves the decision from every future migration to one line, once.

A schema that made the choice once and kept it is easier to read than the rule: a library management model uses TIMESTAMPTZ for every point in time it stores, including the domain timestamps that mark when a loan was issued and when a fine was assessed. The remaining work is the audit of what is already there, and it is a finite list rather than an ongoing discipline. Open the diagram, read the margin, convert the columns that record instants, ignore the handful that genuinely record clock readings, and the question is closed for the life of the schema.

The one-line version

timestamp stores what a clock said, timestamptz stores when something happened, both cost 8 bytes, and the second one is what almost every column in your schema means. Your ORM picked the first, in Rails since forever and in Prisma by default, which is why the columns to fix outnumber the ones you would ever write by hand. A migration linter cannot find them because their statements are years behind you. Read them off the model instead: the schema is the thing that is wrong, so the schema is the thing to check.