Skip to main content

PG.L6.TIMESTAMP_NO_TZ — Timestamp column with no time zone

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL

What the column actually stores​

timestamp without time zone keeps the digits it was given. Nothing else. There is no clock attached, so the value only means something if every writer and every reader already agree on one — and that agreement lives in your head, not in the database.

Two workers in different regions write a row a second apart. The column stores them an hour apart, and nothing in it says so. The value then survives a dump, a restore onto a server with a different TimeZone, and a read replica in a third — unchanged each time, and meaning something different each time.

timestamptz does not store a zone either, despite the name. It stores the instant: PostgreSQL converts the value to UTC on the way in using the session's zone, and converts it back on the way out. Two moments a second apart stay a second apart, whoever wrote them and wherever they are read.

The failure is quiet, and that is why it is worth a rule. Nothing errors. Nothing is corrupted at write time. The mistake surfaces months later as a report that is off by an hour twice a year, on rows written by one region, around a daylight-saving boundary.

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->timestamp('placed_at');
});

Laravel's timestamp() produces exactly this type, which is why this rule sits at level 6: it will have something to say about almost every Laravel schema, and that is a conversation to opt into rather than a build to fail.

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->timestampTz('placed_at');
});

Migrating an existing column converts the values rather than reinterpreting them:

ALTER TABLE orders
    ALTER COLUMN placed_at TYPE timestamptz
    USING placed_at AT TIME ZONE 'UTC';

The USING clause is the part that matters. It states which clock the existing digits were on. Get it wrong and every historical row shifts — so name the zone the application was actually writing in, not the one the server happens to have today.

What is not flagged​

date carries no instant to convert, so it is never reported. Neither is timestamptz or timetz.

A time without time zone is reported, with timetz as its counterpart — but it is the weaker case: a time of day with no date has no instant either, and it is very often a deliberate wall-clock value.

The exception this rule cannot see​

A zone-less column is sometimes exactly right. A branch that opens at 09:00 local opens at 09:00 in every city; converting that to an instant would be the bug, not the fix. Business hours, recurring appointment times and opening calendars are genuine wall-clock data.

This rule reads the catalog, not your domain, so it cannot tell one from the other. Where the zone-less type is deliberate, ignore it and keep the decision visible:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'PG.L6.TIMESTAMP_NO_TZ', 'objects' => ['public.opening_hours']],
        ],
    ],
],

The finding stays counted and listed under what hid it, rather than disappearing from the report.

One finding per table, naming every column​

A table with three zone-less columns produces one finding that names all three, not three findings. That is deliberate: a catalog finding is located at the object, so three findings about one table would be indistinguishable from each other — and it is also the better report. Which columns to convert is one decision about one table, not three separate ones.