Naming time fields

Encode the meaning in the name — _at for instants, _on for dates, _local/_utc when the type can’t say it, units for durations.

A field’s name is read far more often than its type — in a data catalog, a SELECT * in someone’s notebook, a CSV header, an ERD, a dbt model. Whoever reads it three teams away has the name and little else. So the name should answer, to whatever degree the type does not already, three questions: what kind of value this is (an instant, a civil date, a wall-clock datetime, a duration), on what basis (UTC, local, zone-aware), and in what units (for anything numeric). This is the concrete half of Your schema is a contract: the type is the first carrier of meaning, and the name is the second.

Suffixes that carry the kind

A small, consistent vocabulary of suffixes does most of the work:

Two rules keep the vocabulary honest:

• Suffix, not prefix. scheduled_at_utc and scheduled_at_local sort next to each other; utc_scheduled_at and local_scheduled_at scatter across the alphabet and group by basis instead — which is not how anyone browses a schema. Keep the concept at the front.
• _at already implies an instant. Add _utc/_local only when the type cannot say it or the value is deliberately local. Do not write created_at_utc when a timestamptz already guarantees UTC — the tag is the fallback for imperfect types, not a parallel system.

Name the concept, not the mechanism

Reach for a name that says what the value is, not how it happens to be stored. Avoid timestamp as a field name. It implies a Unix-style integer, it collides with the SQL timestamp type keyword — so you end up quoting it and second-guessing whether you mean the type or the value — and it tells a reader nothing about which instant it records. happened_at, last_login_at, or published_at cost nothing extra and answer the question.

Apply _at uniformly, even where it feels redundant: created_at, modified_at, deleted_at. It is tempting to drop it for created or deleted and lean on the type — but the convention’s whole value is that a reader never has to check the type to know what a column is, and a carve-out brings the guessing back. deleted is the worst offender: a bare deleted reads as a boolean, and a nullable deleted timestamp (the standard soft-delete pattern) sitting under a boolean-sounding name is a trap. deleted_at removes the doubt.

Distinguish birthdate from birthday. A birthdate — or date_of_birth / dob, the more conventional names — is a full civil date: year, month, day, no time, no zone. A birthday is the recurring anniversary: a month-day with no year, a different and narrower kind of value. The name should say which you mean, and the type should match — storing a birthdate as a UTC instant is the same mistake the birthday pitfall on Store vs display warns about.

Dates hide a zone; ranges hide a boundary

Two questions hide inside time fields that no suffix fully answers — and where the name cannot, a comment must.

A date hides a zone — sometimes. There are two kinds of date, and they behave oppositely. A date-as-fact — a birthdate, an invoice date, a public holiday, a historical date — is genuinely zoneless and complete; there is no user whose zone resolves it, and inventing one is the bug. A date-as-deadline — expires_on, due_on — is shorthand for an instant, so it carries an unanswered question: which instant does that day become real, and in whose zone? Often a single zone applies (the user’s), and to act on the value you must resolve it there. To email every user at 8 AM on June 12, you store the civil intent — a date plus a target wall-clock time — and resolve it to an instant per user’s zone, sending in a rolling wave as 8 AM arrives around the world. A single stored UTC instant could not do that; it would fire at 8 AM in exactly one place.

A range hides a boundary. before / after, and the valid_from / valid_to / valid_until family, are good for validity windows — X.509 TLS certificates have used notBefore / notAfter for decades. But they smuggle in an inclusivity question: is valid_until = 2026-06-12 inclusive of the 12th or not? Off-by-one bugs live here. State it — in the name where you can, in a comment where you cannot.

When the name can’t hold it, the comment states the protocol

When the type cannot carry the full meaning and the name can only hint at it, write the rest down where it lives forever: a field comment stating the read protocol. A good one is short and operational:

• scheduled_at_local — “wall-clock local; resolve to an instant in the recipient’s zone at send time.”
• created_at_utc — “UTC; the column type is naive — never apply a zone on read.”
• expires_on — “date; access ends at the first instant of the next day in the user’s zone (half-open).”

The comment is the contract’s fine print. It travels with the schema and surfaces in \d+ / information_schema / catalog tools — so it is far less likely to drift than a wiki page. Not because it can’t go stale (it can), but because it lives right next to the thing it describes. See Your schema is a contract.

← Back to all topics