0Pricing
SQL Interview Prep · Lesson

Time Zones and Timestamps

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

Time Zones and Timestamps is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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
);

Store UTC, Convert at the Edges

The golden rule. Persist instants in UTC (use timestamptz), and convert to a local zone only when presenting to a user. This avoids ambiguity around daylight saving and makes ordering by time correct everywhere.

If asked "why UTC?", say: UTC has no daylight-saving shifts, so the same wall-clock value never occurs twice or gets skipped, unlike local time.

-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;

The AT TIME ZONE Double Meaning

AT TIME ZONE is clever and a frequent gotcha because it does two opposite things depending on input type:

  • Applied to a timestamptz, it converts the absolute instant to that zone and returns a plain timestamp (wall clock there).
  • Applied to a plain timestamp, it interprets that wall clock as being in that zone and returns a timestamptz.

Knowing which direction it runs is the whole trick.

-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 21:00:00

-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 03:00:00+00

Getting the Current Instant

Know your "now" functions. NOW() and CURRENT_TIMESTAMP return a timestamptz in Postgres. They return the time of the transaction start, not the statement, which matters in long transactions.

For UTC explicitly, convert: NOW() AT TIME ZONE 'UTC'. In MySQL, UTC_TIMESTAMP() gives UTC directly.

SELECT
  NOW()                       AS tx_start_tz,
  NOW() AT TIME ZONE 'UTC'    AS utc_walltime;

Daylight Saving Is the Real Enemy

Interviewers love DST edge cases. When clocks spring forward, a local wall-clock hour does not exist; when they fall back, an hour repeats. Storing local time makes these ambiguous or invalid.

Storing UTC sidesteps this entirely: every instant is unique and monotonic. Naming a region like 'America/New_York' (not a fixed offset like -05:00) lets the database apply DST rules correctly for any date.

-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
       TIMESTAMPTZ '2024-01-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)

Grouping by Local Day Across Zones

A realistic problem: "daily active users in each user's local time." If you truncate the UTC timestamp directly, midnight boundaries are wrong for non-UTC users.

Convert to the user's zone before truncating to the day. The conversion shifts the wall clock so day boundaries align locally.

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
  COUNT(DISTINCT e.user_id)                         AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;

Comparing Timestamps Safely

When you filter on a timestamptz column, compare against an explicit instant, ideally a UTC literal or a timestamptz with offset. Comparing against a bare string can be interpreted in the unpredictable session zone.

This keeps the comparison unambiguous regardless of who runs the query.

SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2024-04-01 00:00:00+00';

Epoch and Unix Timestamps

Many systems store time as a Unix epoch (seconds since 1970-01-01 UTC). Interviewers may hand you an integer column and ask you to read it.

  • Postgres: TO_TIMESTAMP(epoch_seconds) returns a timestamptz.
  • Back to epoch: EXTRACT(EPOCH FROM occurred_at).
  • MySQL: FROM_UNIXTIME() and UNIX_TIMESTAMP().

Epoch values are inherently UTC, which is part of why they are popular for storage.

SELECT
  TO_TIMESTAMP(1709294400)               AS as_ts,   -- from epoch
  EXTRACT(EPOCH FROM NOW())::bigint        AS as_epoch; -- to epoch

Cross-Dialect Time Zone Notes

A quick map so you sound fluent in any environment:

  • Postgres: timestamptz + AT TIME ZONE, the richest support.
  • MySQL: TIMESTAMP auto-converts via the session time_zone; CONVERT_TZ(t, from, to) converts explicitly. DATETIME has no zone awareness.
  • SQL Server: datetimeoffset stores an offset; AT TIME ZONE 'name' converts using Windows zone names.
-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;

Deeper Example: Sessions Spanning Midnight

A subtle reporting question: count sessions per local calendar day when a session can cross midnight. The fix is the same discipline, convert to local time, then bucket.

Store start and end as timestamptz; for reporting, derive the local day from the converted start. If a session must be split across two days, you would join a day spine, a great point to raise as a follow-up.

SELECT
  DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;

Quick Check

Confirm the recommended storage strategy and why.

Recap: Time Zones and Timestamps

The discipline to walk away with:

  • Store UTC as timestamptz; convert to a named zone only for display.
  • timestamptz stores a UTC instant, not a zone, despite the name.
  • AT TIME ZONE runs in both directions depending on input type: it converts a timestamptz to local wall clock, or interprets a plain timestamp as being in a zone.
  • Use region names ('America/New_York') so DST is applied automatically; avoid fixed offsets.
  • Convert to local time before truncating to a day, and compare columns against explicit UTC instants.

Frequently asked questions

Is the “Time Zones and Timestamps” lesson free?

Yes — the full text of “Time Zones and Timestamps” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Time Zones and Timestamps”?

Storing UTC, converting zones, and the gotchas interviewers raise about timestamps. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Time Zones and Timestamps” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

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