Skip to main content

MY.L6.TIMESTAMP_2038 — A future instant held in a TIMESTAMP column

  • Category: idiom
  • Level: 6
  • Confidence: heuristic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

The type ends in 2038, and a future value gets there first​

MySQL's TIMESTAMP holds nothing after 2038-01-19 03:14:07 UTC. In strict mode, which a Laravel connection turns on, a later value is refused with error 1292 rather than stored. Every TIMESTAMP column reaches that date in time, but a column that holds future instants reaches it today: a license sold with fifteen years to run, a renewal date, a job scheduled far ahead.

Laravel's timestamp(), timestamps() and softDeletes() all declare TIMESTAMP on MySQL.

Flagged​

Schema::create('licenses', function (Blueprint $table) {
    $table->id();
    $table->timestamp('expires_at');   // TIMESTAMP: nothing after 2038-01-19
});

Preferred​

Schema::create('licenses', function (Blueprint $table) {
    $table->id();
    $table->dateTime('expires_at');    // DATETIME: up to 9999-12-31
});

DATETIME reaches 9999-12-31. It stores the value it is given, without the conversion from the session time zone that TIMESTAMP makes, so the session has to run in UTC, which MY.L6.TIME_ZONE_NOT_UTC checks. dateTimeTz() declares the same type on MySQL.

A heuristic on the column name​

A catalog says which type a column has, not which instants it holds, so the rule reads the name word by word. A word that points ahead is reported, and its past form is not:

ReportedNot reported
expires_at, valid_until, due_at, ends_at, starts_at, scheduled_at, next_run_atexpired_at, ended_at, started_at, created_at, updated_at, deleted_at

The words are expires, expire, expiry, expiration, until, due, deadline, ends, starts, scheduled, available, publish, renews, next, retry and remind. One finding names every such column of a table.

False positives​

A name that points ahead over values that do not. The rule reads the name, not the values, so a column named for a future instant that only ever holds past ones is reported anyway. It can stay as it is.

A future instant under another name. valid_to holds the future and carries none of the words, so it is not reported.

Changing the type​

Moving a column from TIMESTAMP to DATETIME rewrites the table. The lint suite classifies that operation when the migration runs and carries its downtime class, so the change is planned like any other rewrite.

Sources​