0Pricing
SQL Interview Prep · Lesson

Time Zones and Timestamps

Storing UTC, converting zones, and the gotchas interviewers raise about timestamps.

Why Time Zones Trip Up Candidates

Time zones are where confident candidates stumble, so interviewers probe them to find depth. The core question is always: "how do you store and compare timestamps across regions?"

The professional answer is a discipline, not a function: store everything in UTC, convert only at the edges for display. Get the storage model right and most queries become trivial.

  • timestamp vs timestamptz
  • Converting between zones
  • UTC as the source of truth

timestamp vs timestamptz

PostgreSQL has two timestamp types, and confusing them is a top interview slip.

  • timestamp (without time zone): a wall-clock value with no zone attached. It stores exactly what you give it.
  • timestamptz (with time zone): stored internally as UTC; on input it converts from the session zone, on output it converts back.

Despite the name, timestamptz does not store a zone; it stores a precise instant in UTC. That detail impresses interviewers.

CREATE TABLE events (
  id          bigint,
  occurred_at timestamptz   -- recommended: an absolute instant
);

All lessons in this course

  1. Date Arithmetic and Intervals
  2. Truncating and Bucketing Dates
  3. Parsing and Formatting Strings
  4. Time Zones and Timestamps
← Back to SQL Interview Prep