skip to content

What does TIMESTAMP WITH TIME ZONE store that a plain TIMESTAMP does not?

level: middleimportance: must knowfreq 60%

answer

  1. One of them is just a reading on a clock
  2. Ask whether the value identifies a moment
  3. Two rows from two continents must be comparable
  4. The missing piece is the offset from UTC
  5. Calendar facts are a different type again

basics

~20 s

A plain TIMESTAMP is a wall-clock reading with no zone, so it does not identify a moment in time. TIMESTAMP WITH TIME ZONE carries the zone offset, so it pins an actual instant and can be compared across regions.

solid answer

~50 s

`TIMESTAMP` — strictly `TIMESTAMP WITHOUT TIME ZONE` — stores a date and a time of day and nothing else. `2026-03-01 09:00:00` in such a column is a wall-clock reading: it does not say *which* 09:00, so two rows written from different regions are not comparable and no arithmetic on them is meaningful across zones. `TIMESTAMP WITH TIME ZONE` adds the offset from UTC, which is what turns a reading into an instant, so ordering, differences and comparisons are well defined globally. Use `WITH TIME ZONE` (or a rigidly enforced UTC convention) for anything that records when something *happened*: created_at, logins, payments, audit rows. Use plain `DATE` for calendar facts such as a birthday or an invoice date, where no time or zone is involved, and `TIME` for a time of day divorced from any date. Engines diverge on how `WITH TIME ZONE` is implemented, so confirm your engine's behaviour.

code

sql · 11 lines
sql
CREATE TABLE account_event (
  account_id  BIGINT NOT NULL,
  occurred_at TIMESTAMP(3) WITH TIME ZONE NOT NULL, -- an instant
  birth_date  DATE,                                  -- a calendar fact
  opens_at    TIME                                   -- clock time, no date
);

-- Meaningful only because occurred_at identifies an instant
SELECT account_id
FROM   account_event
WHERE  occurred_at >= CURRENT_TIMESTAMP - INTERVAL '1' DAY;

go deeper

for a junior

Know the four types and what each holds: DATE is a calendar date, TIME a clock time, TIMESTAMP a date plus time with no zone, and WITH TIME ZONE the version that actually pins a moment.

for a middle

Explain wall-clock versus instant, why two plain timestamps written from different regions cannot be compared, and which column in a schema you would give each type, including DATE for calendar facts.

for a senior

Demonstrate the operational judgment: a fixed convention for storing instants, awareness that engines implement WITH TIME ZONE differently, and the diagnosis of bugs where a value shifts by hours or a date lands on the wrong day.

for a principal

Own the cross-service temporal contract — one representation for instants, a stated rule for calendar dates and future local events, and the reasoning about why a stored offset is not a stored zone when scheduling rules can change.

## The four portable temporal types Standard SQL gives you `DATE`, `TIME(p)`, `TIMESTAMP(p)`, and the `WITH TIME ZONE` variants of the latter two. The optional `p` is fractional-seconds precision — digits after the seconds — defaulting to 0 for `TIME` and 6 for `TIMESTAMP`. - **`DATE`** — a calendar date: year, month, day. No time, no zone. - **`TIME`** — a time of day with no date. - **`TIMESTAMP`** (i.e. `WITHOUT TIME ZONE`) — a date plus a time of day, still with no zone. - **`TIMESTAMP WITH TIME ZONE`** — the same, plus the offset from UTC. ## Wall clock versus instant The crucial distinction is between a **wall-clock reading** and an **instant**. A wall-clock reading is what a clock on some wall showed: `2026-03-01 09:00:00`. Which wall is not recorded. If one service writes that value from Berlin and another from São Paulo, the two rows sort as equal and subtract to zero, even though the events were hours apart. That is what `TIMESTAMP WITHOUT TIME ZONE` stores. An instant is a point on the global timeline, identified by the wall-clock reading *plus* the offset that maps it to UTC: `2026-03-01 09:00:00+01:00`. Two instants written anywhere in the world compare and subtract correctly. That is what `WITH TIME ZONE` gives you. So the litmus for choosing is: does this column record **when something happened**, or **what the calendar or clock said**? Event times — created_at, logged_in_at, paid_at, audit rows, anything you will later order, window over, or compare against `CURRENT_TIMESTAMP` — need an instant. Calendar facts — a date of birth, a public holiday, a contract start date — are not instants at all and belong in `DATE`, where storing a time and a zone would only add fake precision. ## Implementations diverge, and it matters The standard says `WITH TIME ZONE` retains an offset. Real engines take different routes: - **PostgreSQL** does not store the offset. `timestamptz` converts the input to UTC on the way in, stores that, and renders it in the session's `TimeZone` on the way out. The instant is exact; the originating offset is lost. `timestamp` (without zone) stores the reading verbatim and performs no conversion. - **MySQL** has no `WITH TIME ZONE` type. Its `TIMESTAMP` column converts to UTC using the session `time_zone` on write and back on read — so it behaves like an instant — while `DATETIME` stores the reading verbatim with no conversion. `TIMESTAMP` also has a much narrower supported range than `DATETIME`. - **SQLite** has no dedicated date or time type at all; values live in TEXT, REAL or INTEGER and the built-in date functions interpret them. Because of that spread, any schema meant to run on more than one engine should fix a convention — normally *store instants in UTC* — and enforce it in one place, rather than assuming the type name carries the same semantics everywhere. ## The offset is not the zone An offset like `+01:00` says how far a reading is from UTC at that moment. A *zone*, such as `Europe/Berlin`, is a rule that maps instants to offsets over time, including daylight-saving transitions and political changes. For a past event the offset is enough, because the instant is settled. For a **future local appointment** it is not: if a legislature moves a DST boundary, the correct instant for "09:00 local on 25 October next year" changes. Scheduling systems therefore often store the local wall-clock timestamp alongside a zone name in a separate column, and resolve the instant when the appointment is due. ## `TIME WITH TIME ZONE` The standard defines it; it is rarely useful. A time of day with an offset but no date cannot be resolved against daylight-saving rules, because the rules depend on the date. Prefer `TIME` for a plain clock time, and a full timestamp when you need an instant. ## Practical checklist - Recording when something happened → an instant type, or UTC by strict convention. - Calendar facts with no time component → `DATE`. - Clock time with no date, such as opening hours → `TIME`. - Future local events → wall-clock timestamp plus a stored zone name. - Fractional seconds → declare the precision you need, e.g. `TIMESTAMP(3)`, rather than accepting a default that may differ between engines.

  • Which SQL type would you use for a customer's date of birth, and why not a timestamp?
    `DATE`. A birth date is a calendar fact, not an instant: no time of day is known or wanted, and attaching a zone invents precision the data never had. Storing it as a timestamp also invites conversion bugs, where a value near midnight shifts to the previous or next day when it is rendered in another session's zone.
  • If a team stores every event as a plain TIMESTAMP in UTC by convention, what are they giving up?
    The convention can work, but it is enforced by discipline rather than by the type: nothing stops a service writing local time, and the database cannot convert for display or detect a mistake. You also lose the engine's zone-aware rendering and comparison. It is workable in a single-service system with strict review, and fragile once several writers exist.
  • Why do scheduling systems store a zone name rather than just an offset for future appointments?
    An offset describes one moment; a zone name is a rule that maps instants to offsets over time. Daylight-saving boundaries and political changes can move, so an instant computed today for a local time next year may become wrong. Keeping the local wall-clock time plus the zone lets the correct instant be recomputed when the rules change.

A plain TIMESTAMP is a photograph of a clock face; TIMESTAMP WITH TIME ZONE is that photograph with the city written on the back.

saying these in an interview costs you the question

  • Says a plain TIMESTAMP is implicitly in UTC
  • Treats a fixed offset as equivalent to a named time zone
  • Stores a date of birth as a timestamp with time zone
  • Assumes every engine implements WITH TIME ZONE the same way
  • Compares wall-clock timestamps written from different regions

context

resolving links…