AT TIME ZONE UTC Does Two Opposite Things. Your ORM Picks One.

A wall of identical clock faces, every one showing a different time, none of them labelled with a city. That is a timestamp column in PostgreSQL: a reading, with no statement about which hour on Earth produced it. We read the source of fourteen ORMs and query builders this week to see which ones hand you that column by default. Eight of them do.

TL;DR

• AT TIME ZONE UTC is not one operator. On a naive timestamp it adds a zone; on a timestamptz it removes one. Same text, inverse operations, and the query gives no hint which is running.
• We read the source of 14 ORMs. Eight default to a column with no time zone: Prisma, Drizzle, TypeORM, SQLAlchemy, Rails, Laravel, Doctrine DBAL and Peewee. Six default to timestamptz: Django, Sequelize, Knex, MikroORM, GORM and Ent.
• An ALTER COLUMN ... TYPE timestamptz with no USING clause silently reinterprets every existing row as session local time, shifting summer rows and leaving winter rows alone. No single constant offset repairs it afterwards.
• The session zone comes from your client library. pgjdbc sends the JVM’s zone on connect. node-postgres sends nothing at all. Two services, same database, different answers.

One operator, two opposite meanings

Table 9.34 of the PostgreSQL manual, in the section on AT TIME ZONE, lists the variants plainly. Applied to a timestamp without time zone, the operator “converts given time stamp without time zone to time stamp with time zone, assuming the given value is in the named time zone”. Applied to a timestamp with time zone, it “converts given time stamp with time zone to time stamp without time zone, as the time would appear in that zone”.

Read those twice. The first asserts a zone onto a value that had none. The second discards the zone and gives you back a wall-clock reading. One expression, created_at AT TIME ZONE 'UTC', does one thing or the exact reverse depending on the declared type of the column, and it returns a different type in each case. Change the column type in a migration and the query keeps running, quietly answering a different question.

It helps to stop thinking of these as two formats of the same thing. For a zoned value the manual is explicit: “the value is stored internally as UTC, and the originally stated or assumed time zone is not retained”. A timestamptz is an instant, a point on the universal timeline, rendered for display in whatever zone you ask for. A timestamp is a wall-clock reading with no instant attached at all. They are different kinds of fact, and only one of them can answer “when did this actually happen”.

We read the source of fourteen ORMs

We went to the code rather than the documentation, because documentation drifts. For each library we found the function that emits the Postgres DDL type for the framework’s ordinary datetime field, with no options passed.

Default to no time zone (8):

• Prisma: DATE_TIME_DEFAULT is Timestamp(Some(3)) in the Postgres connector, so DateTime becomes timestamp(3).
• Drizzle ORM 0.45.3: the builder takes config?.withTimezone ?? false, and getSQLType() appends “with time zone” only when that flag is set.
• TypeORM 1.1.1: a Date property normalises to the literal string timestamp without time zone, and createDate, updateDate and deleteDate all map to timestamp.
• SQLAlchemy: DateTime.__init__ is declared timezone: bool = False, and the Postgres compiler renders TIMESTAMP WITHOUT TIME ZONE.
• Rails / ActiveRecord: class_attribute :datetime_type, default: :timestamp in the Postgres adapter.
• Laravel: typeTimestamp() returns a string ending in without time zone, and the ubiquitous timestamps() helper calls it for both created_at and updated_at.
• Doctrine DBAL 4.4: getDateTimeTypeDeclarationSQL() returns TIMESTAMP(0) WITHOUT TIME ZONE.
• Peewee: PostgresqlDatabase.field_types maps DATETIME to TIMESTAMP.

Default to time zone aware (6): Django ("DateTimeField": "timestamp with time zone"), Sequelize 6.37.8 (DATE.toSql() returns TIMESTAMP WITH TIME ZONE), Knex 3.3.0 (useTz defaults to true), MikroORM 7.2.2 (timestamptz), GORM’s Postgres driver (schema.Time becomes timestamptz) and Ent (field.TypeTime resolves to the “with time zone” constant).

Eight to six is the finding. There is no industry default here to fall back on. The type of your most load-bearing audit column was chosen by whoever picked the framework, and in most teams that decision was made for entirely unrelated reasons.

Two details are worth pulling out. TypeORM sets createDateDefault to now(), and now() returns timestamp with time zone. Writing it into a naive column triggers the implicit conversion, which the manual says is “taken or given as timezone local time”. Your created_at is therefore the database session’s wall clock, not UTC, and it looks identical either way. Separately, when Drizzle reads a non-zoned column back it appends the literal string +0000 before constructing a JavaScript Date. The ORM assumes the value is UTC. The column never made that promise. We covered the language half of this problem in August when Temporal reached Stage 4; this is the storage half, and it is the one that survives a redeploy.

The migration that moves every row

Eventually somebody notices the column type and fixes it:

ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz;

That statement succeeds. It also rewrites every row, because with no USING clause Postgres applies its normal conversion rule, and the manual states that rule without ambiguity: conversions between the two types “normally assume that the timestamp without time zone value should be taken or given as timezone local time”. If your application wrote UTC but the session zone is Europe/Dublin, every value now claims to be Irish local time.

The conversion uses the offset appropriate to each individual value’s date, so rows written in July shift by an hour and rows written in January do not. There is no constant you can subtract afterwards to undo it. A blanket “take an hour off everything” repair fixes the summer and breaks the winter, which is how a one-hour bug becomes a permanent one. The correct statement carries the assertion explicitly:

ALTER TABLE events
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'UTC';

Two services, two session time zones

Every one of these behaviours depends on the session TimeZone setting, and almost nobody sets that deliberately. It arrives from the client library.

The PostgreSQL JDBC driver sends it as a startup parameter on every connection, taken straight from TimeZone.getDefault().getID(), which is the JVM’s zone and in practice the container’s zone. node-postgres 8.23.0 sends no TimeZone parameter at all; searching its library directory for the string returns nothing, so a Node connection inherits whatever the server was configured with.

A Node service writes the rows. A JVM-based migration tool later runs the ALTER TABLE. The conversion uses the migration tool’s container zone, which nobody involved in the schema change has ever looked at, and the result is an hour of drift on a subset of rows with no error and no log line. If those rows are an incident timeline you will one day hand to a regulator under the Cyber Resilience Act reporting clock, or a payment audit trail, an unlabelled hour is not a cosmetic defect. It is the difference between meeting a deadline and appearing not to have.

What to do this week

This is a two-hour audit, not a project.

1. Find out what you actually have. Query information_schema.columns for every column whose data_type starts with timestamp and read the split. Do not guess from the model file; read the database.
2. Check the session zone from the application, not from psql. Run SHOW TimeZone; through each service’s own connection pool.
3. Make new columns zoned by default. Drizzle takes { withTimezone: true }, Prisma @db.Timestamptz(3), TypeORM type: 'timestamptz', Rails self.datetime_type = :timestamptz, Laravel timestampsTz(), SQLAlchemy DateTime(timezone=True).
4. Never run ALTER COLUMN ... TYPE on a timestamp without a USING clause. Make that a blocking review rule. Rehearse it against a restored copy and diff a sample of rows before and after.
5. Keep the naive type where it is genuinely correct. A shop’s opening hour, a recurring 07:00 alarm, a hotel check-in time: these are wall-clock facts that should not be pinned to an instant, and converting them to timestamptz is its own category of bug. The rule is not “always zoned”, it is “decide, and write the decision down”.

Most teams we talk to have never checked, and find both types in the same schema, written by different services, compared against each other in a report somebody trusts.

REPTILEHAUS does this kind of work: schema audits, migration rehearsal, and the unglamorous database and DevOps groundwork that stops a one-hour discrepancy becoming a compliance conversation. If you are not sure which type your audit tables use, that is the answer already. Get in touch and we will take a look.

📷 Photo by Donald Wu on Unsplash