SQL Date & Time Deep Dive: Time Zones, Intervals, Truncation & Windowing

sql date time intervals is the primitive every dashboard, every cohort analysis, every CDC replay, every retention chart, and every finance close report eventually stumbles on — the wrong time zone that makes the CEO's "yesterday's sales" chart show tomorrow's data in New York, the BETWEEN boundary bug that misses events on the last day of the month, the DST spring-forward that creates a duplicate hour in the middle of March, the INTERVAL '1 month' semantic that turns Jan 31 into Feb 28. Every SQL engineer knows NOW() and CURRENT_DATE on day one; knowing why TIMESTAMP WITH TIME ZONE actually stores UTC (not the original time zone), why WHERE ts BETWEEN '2026-07-01' AND '2026-07-31' misses events on 2026-07-31 20:00:00, and how to use DATE_TRUNC correctly for bucketed rollups is what separates a senior SQL candidate from everyone else. This guide is the honest tour of date/time semantics across 8 engines.

The tour walks the four DE date pathologies — DST off-by-one-hour that corrupts a report every March and November, UTC-vs-local confusion where you store one and display the other, closed-vs-open window ambiguity that produces the "missing last day" bug, and truncation surprises where DATE_TRUNC('month', '2026-07-31 23:59') gives you July 1, not July 31. It walks TIMESTAMP WITH TIME ZONE (which actually stores UTC internally and converts on read) vs TIMESTAMP WITHOUT TIME ZONE (which stores wall clock with no zone info), why UTC-first storage is the industry standard, and how AT TIME ZONE 'America/Los_Angeles' converts. It walks INTERVAL arithmetic (ts + INTERVAL '1 day'), the engine-specific DATE_ADD / DATEADD / TIMESTAMPADD variants, and the month-boundary edge cases (Jan 31 + 1 month = Feb 28 on most engines, sometimes error on others). It walks DATE_TRUNC for bucketing timestamps to hour / day / month / quarter, the canonical half-open [start, end) window pattern (WHERE ts >= '2026-07-01' AND ts < '2026-08-01') that beats BETWEEN in every way, and finally the 8-engine dialect matrix. Every section ships a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen SQL aggregation drills → for time-series bucketing patterns, and layer SQL optimization drills → for reading time-range query plans.

---

On this page

• Why date/time semantics matter in 2026
• Time zones + TIMESTAMP WITH / WITHOUT TIME ZONE
• Intervals + DATE_ADD / DATE_SUB
• Truncation + DATE_TRUNC + half-open windows
• Dialect matrix + patterns
• Cheat sheet — date & time recipe list
• Frequently asked questions
• Practice on PipeCode

---

1. Why date/time semantics matter in 2026

The sql date time intervals mental model — why "just use timestamps" doesn't get you halfway to correctness, and what senior interviewers actually probe

The one-sentence invariant: every date/time bug in production comes from one of four failure modes — wrong time zone, wrong window inclusivity, wrong truncation grain, or wrong interval arithmetic on month boundaries — and the discipline is (1) store UTC, (2) convert on read, (3) always use half-open windows [start, end), (4) always specify the grain with DATE_TRUNC, and (5) know your engine's INTERVAL semantics because they diverge on month edge cases.

Where date/time bugs actually show up.

• Dashboards "yesterday's sales" shows tomorrow. A New York analyst opens a Sydney-timezone dashboard; timestamps interpreted in wrong TZ.
• BETWEEN misses last day. WHERE ts BETWEEN '2026-07-01' AND '2026-07-31' — misses timestamps at 2026-07-31 12:00 because '2026-07-31' treats as 00:00:00.
• DST spring-forward gap. 2:30 AM on the second Sunday of March doesn't exist in US Eastern.
• DST fall-back overlap. 1:30 AM on the first Sunday of November happens twice; SQL can't distinguish them.
• Month-boundary INTERVAL. '2026-01-31' + INTERVAL '1 month' = 2026-02-28 (clamped). '2026-02-28' + INTERVAL '1 year' = 2027-02-28 (fine). '2024-02-29' + INTERVAL '1 year' = 2025-02-28 (leap year edge).
• Half-day off. Session TZ is US/Eastern; storage is UTC; NOW() returns something confusing.
• Aggregation grain wrong. SUM(revenue) GROUP BY DATE(ts) — bucketed by UTC day; user wanted local-TZ day.
• CDC replay clock skew. Downstream event timestamps drift when clocks aren't synced.

The four DE date pathologies.

• DST off-by-one-hour. Twice a year, DST changes; if your TZ handling is wrong, one hour is duplicated or missing. Every event pipeline running through March / November has faced this.
• UTC vs local confusion. Storage is UTC; display is local. If either is wrong, timestamps drift.
• Closed vs open window. [start, end] is closed (inclusive both ends). [start, end) is half-open (inclusive start, exclusive end). BETWEEN is closed; but with fractional-second timestamps, half-open is what you want.
• Truncation surprises. DATE_TRUNC('month', '2026-07-31') returns 2026-07-01 (start of month), not July 31. If you're mapping to "which month" for aggregation, that's right; if you're doing arithmetic, it's a surprise.

What senior interviewers actually probe.

• Do you store UTC? The correct answer is yes, always. Store UTC; convert on read.
• Do you know TIMESTAMP WITH TIME ZONE stores UTC? Not the input TZ. The engine converts and stores as UTC internally.
• Do you use half-open windows? WHERE ts >= '2026-07-01' AND ts < '2026-08-01'. Not BETWEEN.
• Do you know DATE_TRUNC? For bucketing time-series into hour / day / month / etc.
• Do you know INTERVAL semantics on month boundaries? Jan 31 + 1 month = Feb 28 (clamp).
• Do you know DST gap/overlap? Spring forward creates a 1-hour gap; fall back creates a 1-hour duplicate.
• Do you know EXTRACT? For pulling year, month, day out of a timestamp.
• Do you know how to convert time zones? AT TIME ZONE 'America/Los_Angeles' (Postgres) or CONVERT_TZ() (MySQL).
• Do you know Unix epoch? EXTRACT(EPOCH FROM ts) returns seconds since 1970-01-01 UTC.

The five golden rules.

• Store UTC. Always. In TIMESTAMP WITH TIME ZONE (Postgres) / TIMESTAMPTZ / equivalents.
• Convert on read. ts AT TIME ZONE 'America/Los_Angeles' for display.
• Half-open windows. WHERE ts >= start AND ts < end. Not BETWEEN.
• DATE_TRUNC for bucketing. Not naive DATE().
• INTERVAL for arithmetic. Not + 86400 on epoch seconds.

Worked example — the BETWEEN boundary bug

Detailed explanation. WHERE ts BETWEEN '2026-07-01' AND '2026-07-31' misses timestamps on 2026-07-31 after midnight because the date literal treats as 2026-07-31 00:00:00.

Question. Show the bug and the fix.

Input.

id	ts
1	2026-07-01 09:00
2	2026-07-31 12:00
3	2026-07-31 23:59
4	2026-08-01 00:00

Code.

-- Buggy: misses id=2 and id=3
SELECT * FROM events
WHERE ts BETWEEN '2026-07-01' AND '2026-07-31';
-- returns: id=1 only

-- Fix 1: half-open window (correct, portable)
SELECT * FROM events
WHERE ts >= '2026-07-01' AND ts < '2026-08-01';
-- returns: id=1, 2, 3

-- Fix 2: DATE_TRUNC comparison (clean for month-level buckets)
SELECT * FROM events
WHERE DATE_TRUNC('month', ts) = DATE '2026-07-01';
-- returns: id=1, 2, 3

Step-by-step explanation.

1. BETWEEN '2026-07-01' AND '2026-07-31' — end date literal treats as midnight of 2026-07-31. Timestamps after midnight of 31st are excluded.
2. Fix 1 — half-open [2026-07-01, 2026-08-01). Includes all of July, excludes 2026-08-01.
3. Fix 2 — DATE_TRUNC('month', ts). Bucketed comparison. Same result.
4. Half-open is the industry-standard pattern.
5. Never write BETWEEN for timestamp ranges. Always use >= start AND < end.

Output.

Query	Rows
BETWEEN	1
Half-open	1, 2, 3
DATE_TRUNC	1, 2, 3

Rule of thumb. For any timestamp range, use half-open. Never BETWEEN.

Worked example — the time zone conversion

Detailed explanation. Store UTC, convert on read.

Code.

-- Storage: UTC in TIMESTAMPTZ
CREATE TABLE events (
  id INT,
  event_at TIMESTAMPTZ NOT NULL   -- Postgres shorthand for TIMESTAMP WITH TIME ZONE
);

INSERT INTO events VALUES (1, '2026-07-12 14:00:00+00');    -- explicit UTC
INSERT INTO events VALUES (2, '2026-07-12 07:00:00-07');    -- LA time, stored as UTC 14:00

-- Convert on read
SELECT
  event_at AS raw_utc,
  event_at AT TIME ZONE 'America/Los_Angeles' AS la_time,
  event_at AT TIME ZONE 'Asia/Tokyo' AS tokyo_time,
  event_at::TIMESTAMPTZ AT TIME ZONE 'UTC' AS utc_display
FROM events;

Step-by-step explanation.

1. TIMESTAMPTZ stores UTC internally, regardless of input TZ.
2. AT TIME ZONE 'X' converts UTC to zone X.
3. Both rows resolve to same UTC time; different display in each TZ.
4. Never trust the raw display without knowing session TZ.

Output. Each event shown in three timezones.

Rule of thumb. UTC in storage; convert on read.

Worked example — INTERVAL '1 month' clamping

Code.

SELECT
  DATE '2026-01-31' + INTERVAL '1 month' AS jan31_plus_month,
  -- Postgres: 2026-02-28. Clamped to last day of Feb.
  DATE '2026-02-28' + INTERVAL '1 year' AS feb28_plus_year,
  -- 2027-02-28. Regular.
  DATE '2024-02-29' + INTERVAL '1 year' AS leap29_plus_year;
  -- 2025-02-28. Leap year edge; clamped.

Step-by-step explanation.

1. Jan 31 + 1 month should be Feb 31, which doesn't exist. Postgres clamps to Feb 28 (or Feb 29 in leap year).
2. Feb 28 + 1 year → Feb 28 next year. Consistent.
3. Feb 29 (leap) + 1 year → Feb 28 in non-leap. Clamped down.
4. Warning — some engines may error on invalid dates; Postgres clamps.
5. For month arithmetic, always test edge cases.

Output. See above.

Rule of thumb. INTERVAL month arithmetic clamps to valid dates. Test month boundaries in your engine.

sql date time intervals interview question on date range design

A senior interviewer asks: "Design a monthly rollup query that reports revenue per month, handles time zones correctly, and uses half-open windows to avoid boundary bugs. Explain each choice."

Solution Using UTC storage + AT TIME ZONE + DATE_TRUNC + half-open windows

-- Assume events.event_at is TIMESTAMPTZ (stored UTC)
-- Report timeline: July 2026 in America/Los_Angeles

WITH la_events AS (
  SELECT
    id,
    event_at,
    event_at AT TIME ZONE 'America/Los_Angeles' AS event_at_local,
    revenue
  FROM events
  WHERE event_at >= (TIMESTAMP '2026-07-01 00:00:00' AT TIME ZONE 'America/Los_Angeles')
    AND event_at <  (TIMESTAMP '2026-08-01 00:00:00' AT TIME ZONE 'America/Los_Angeles')
)
SELECT
  DATE_TRUNC('month', event_at_local) AS month_start,
  SUM(revenue) AS revenue
FROM la_events
GROUP BY DATE_TRUNC('month', event_at_local)
ORDER BY month_start;

Step-by-step trace.

Step	Reason
1	Half-open window [2026-07-01, 2026-08-01) in LA time
2	AT TIME ZONE converts LA-local literals to UTC for comparison
3	AT TIME ZONE on event_at converts back to LA for grouping
4	DATE_TRUNC('month', ...) buckets to first of month
5	GROUP BY + SUM

Output: Monthly revenue in LA-local months; boundary correct; DST-safe.

Why this works — concept by concept:

• UTC storage — event_at is UTC. Any client can compute local time correctly.
• Filter converts LA literal to UTC — the WHERE clause uses UTC comparisons on UTC data.
• AT TIME ZONE for display grouping — event_at converted to LA when grouping.
• Half-open window — no BETWEEN boundary bug.
• DATE_TRUNC — clean grain, no formatting.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

---

2. Time zones + TIMESTAMP WITH / WITHOUT TIME ZONE

The timestamp with time zone model — why WITH TIME ZONE actually stores UTC, and why WITHOUT is almost always wrong

The mental model in one line: TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ / TIMESTAMP_TZ) does NOT store the input's time zone — it stores the equivalent UTC value and converts to session TZ on read, giving you an unambiguous absolute time; TIMESTAMP WITHOUT TIME ZONE stores wall-clock digits with no zone info, so the same string means different absolute times in different sessions — always prefer WITH TIME ZONE for anything that represents an actual moment in time.

Slot 1 — TIMESTAMP WITH TIME ZONE.

• What it stores. UTC internally, regardless of input TZ.
• What input formats work. '2026-07-12 14:00+00', '2026-07-12 07:00-07', '2026-07-12 14:00Z'. All valid.
• What output looks like. Session TZ display. Set via SET TIMEZONE = 'UTC' (Postgres) or connection option.
• What operations preserve TZ. All arithmetic. ts + INTERVAL '1 day' works.
• Postgres alias. TIMESTAMPTZ. Snowflake TIMESTAMP_TZ. BigQuery TIMESTAMP.
• Best for. Any moment-in-time data — user events, log timestamps, transaction records.

Slot 2 — TIMESTAMP WITHOUT TIME ZONE.

• What it stores. Wall-clock digits: year, month, day, hour, minute, second, fraction.
• What's missing. Time zone.
• Consequence. Same value means different absolute times in different sessions.
• When to use. For local wall-clock intent — office hours, holiday dates, business calendar.
• Common misuse. Storing "when a user clicked" as WITHOUT TZ.
• Postgres alias. TIMESTAMP. Snowflake TIMESTAMP_NTZ or TIMESTAMP_LTZ (local-TZ variant).

Slot 3 — AT TIME ZONE.

• ts AT TIME ZONE 'X' — the conversion primitive.
• On TIMESTAMPTZ. Converts the UTC-stored value to zone X, returning the wall-clock in X (TIMESTAMP WITHOUT TIME ZONE).
• On TIMESTAMP (no TZ). Interprets the value as if it were in zone X, returns TIMESTAMPTZ (UTC).
• Common patterns.
  • event_at AT TIME ZONE 'America/Los_Angeles' — display LA time.
  • '2026-07-12 09:00'::TIMESTAMP AT TIME ZONE 'America/Los_Angeles' — parse LA time, get UTC.
• Chain to display. event_at AT TIME ZONE 'UTC' — force UTC display.

Slot 4 — session TZ vs data TZ.

• Session TZ. Per-connection setting. Postgres: SHOW TIMEZONE; / SET TIMEZONE = 'UTC';.
• Default. Comes from server config; often confusing.
• Best practice. Set SET TIMEZONE = 'UTC' in app connections. Explicit.
• On display. TIMESTAMPTZ values shown in session TZ.
• Different clients different views. UTC in JSON API; local in dashboard.

Slot 5 — DST transitions.

• Spring forward. 2:00 AM local time becomes 3:00 AM. The 2:00 AM hour doesn't exist.
• Fall back. 2:00 AM local time becomes 1:00 AM. The 1:00 AM hour happens twice.
• In UTC. No DST. Clean linear time.
• AT TIME ZONE handling. Spring forward — non-existent times get error or shifted. Fall back — ambiguous; usually picks first occurrence.
• Best practice. Store UTC. DST is a display concern.

Slot 6 — TZ name vs offset.

• Name — 'America/Los_Angeles'. IANA/Olson timezone identifier. Handles DST correctly.
• Offset — '-07:00'. Fixed offset. Doesn't handle DST.
• Use name for regions with DST. Otherwise offset works.
• Ambiguity — 'PST', 'PDT'. Abbreviations. Postgres accepts; ambiguous for regions with DST. Prefer IANA name.
• UTC and Z. 'UTC', 'Z', '+00:00' — all equivalent, no DST.

Slot 7 — extracting components.

• EXTRACT(YEAR FROM ts) — returns year.
• EXTRACT(MONTH FROM ts) — returns month (1-12).
• EXTRACT(DAY FROM ts) — day of month (1-31).
• EXTRACT(HOUR FROM ts) — hour (0-23).
• EXTRACT(EPOCH FROM ts) — Unix epoch seconds.
• EXTRACT(DOW FROM ts) — day of week (0=Sunday to 6=Saturday on Postgres; varies).
• EXTRACT(WEEK FROM ts) — ISO week.
• EXTRACT(QUARTER FROM ts) — quarter (1-4).
• Alternative — DATE_PART('year', ts) on Postgres. Same result.

Slot 8 — current time functions.

• NOW() / CURRENT_TIMESTAMP — current tx start time (Postgres). Constant during the tx.
• CLOCK_TIMESTAMP() — current wall clock. Changes during tx.
• STATEMENT_TIMESTAMP() — current statement start.
• CURRENT_DATE — today's date in session TZ.
• CURRENT_TIME — time of day only.

Slot 9 — TZ-aware storage cost.

• TIMESTAMPTZ same storage size as TIMESTAMP. Both 8 bytes internally on Postgres.
• Session TZ conversion is compute. Small; usually negligible.
• Function-based indexes. CREATE INDEX ON t ((event_at AT TIME ZONE 'America/Los_Angeles')) — for LA-local queries. Rarely needed if you filter correctly.

Slot 10 — engine variations.

• Postgres. TIMESTAMPTZ (WITH), TIMESTAMP (WITHOUT). Both native.
• MySQL. TIMESTAMP (WITH TZ, stored UTC, converted to session TZ). DATETIME (WITHOUT).
• SQL Server. DATETIMEOFFSET (WITH), DATETIME2 (WITHOUT).
• Oracle. TIMESTAMP WITH TIME ZONE (stores TZ), TIMESTAMP WITH LOCAL TIME ZONE (stores UTC).
• Snowflake. TIMESTAMP_TZ (with), TIMESTAMP_NTZ (without), TIMESTAMP_LTZ (with local).
• BigQuery. TIMESTAMP (UTC-stored, no TZ metadata). DATETIME (local).

Common beginner mistakes

• Storing user event times as WITHOUT TIME ZONE.
• Not setting session TZ on connections.
• Assuming AT TIME ZONE converts input TZ to output TZ — it converts UTC to output.
• Using TZ abbreviations (PST) instead of IANA names.
• Not testing DST transitions.

Worked example — reproducing the DST spring-forward gap

Detailed explanation. On March 8, 2026 in US/Eastern, at 2:00 AM, clock jumps to 3:00 AM. That 2:30 AM never happens.

Code.

SET TIMEZONE = 'America/New_York';

-- What does 2026-03-08 02:30 mean in Eastern?
SELECT '2026-03-08 02:30:00'::TIMESTAMPTZ;
-- Postgres: shifts to '2026-03-08 03:30:00-04' (during DST)

-- What is 2:00 AM UTC on that day?
SELECT '2026-03-08 02:00:00 UTC'::TIMESTAMPTZ AT TIME ZONE 'America/New_York';
-- returns: 2026-03-07 21:00:00 EST (before DST kicks in)

-- What is 3:00 AM UTC?
SELECT '2026-03-08 07:00:00 UTC'::TIMESTAMPTZ AT TIME ZONE 'America/New_York';
-- returns: 2026-03-08 03:00:00 EDT (after DST)

Step-by-step explanation.

1. Non-existent local time 2:30 AM auto-shifts.
2. UTC storage avoids the ambiguity entirely.
3. In queries, always compute in UTC; convert for display.

Output. DST shift visible.

Rule of thumb. Store UTC. Handle DST only at display time.

Worked example — session TZ affects TIMESTAMP display

Code.

CREATE TABLE events (id INT, event_at TIMESTAMPTZ);
INSERT INTO events VALUES (1, '2026-07-12 14:00 UTC');

SET TIMEZONE = 'UTC';
SELECT * FROM events;
-- 1 | 2026-07-12 14:00:00+00

SET TIMEZONE = 'America/Los_Angeles';
SELECT * FROM events;
-- 1 | 2026-07-12 07:00:00-07

SET TIMEZONE = 'Asia/Tokyo';
SELECT * FROM events;
-- 1 | 2026-07-12 23:00:00+09

Step-by-step explanation.

1. Same stored UTC value; different display.
2. Session TZ setting is the display switch.
3. In APIs, force SET TIMEZONE = 'UTC' for consistency.

Output. Same event in three timezones.

Rule of thumb. Set session TZ explicitly. Never rely on server default.

timestamp with time zone interview question on TZ-safe schema

A senior interviewer asks: "Design a schema for a global e-commerce site with users in 20 time zones. What columns are TIMESTAMPTZ vs TIMESTAMP, and why?"

Solution Using UTC-first storage with per-column TZ intent

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  order_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),      -- absolute moment
  shipped_at TIMESTAMPTZ,                            -- absolute moment
  delivery_scheduled_local TIMESTAMP,                -- local wall clock
  delivery_tz TEXT DEFAULT 'UTC',                    -- TZ name for local
  total NUMERIC NOT NULL,
  status TEXT NOT NULL
);

Step-by-step trace.

Column	Type	Why
order_at	TIMESTAMPTZ	Absolute moment in time; global users
shipped_at	TIMESTAMPTZ	Same
delivery_scheduled_local	TIMESTAMP	Wall-clock intent — "delivered by 5 PM local time"
delivery_tz	TEXT	Companion TZ for local scheduling

Output: Correct schema per column intent.

Why this works — concept by concept:

• TIMESTAMPTZ for absolute moments — order/ship times are single points on the timeline.
• TIMESTAMP + separate TZ column for scheduling — "delivery by 5 PM" is wall-clock intent; the TZ is a separate concern.
• Default NOW() — insert-time UTC.
• Separation of concerns — never conflate wall-clock intent with absolute time.

SQL
Topic — SQL
SQL practice library

Practice →

SQL Topic — aggregation SQL aggregation drills

Practice →

---

3. Intervals + DATE_ADD / DATE_SUB

sql intervals and date_add — ANSI INTERVAL syntax vs engine-specific date arithmetic functions

The mental model in one line: ts + INTERVAL '1 day' is the ANSI-standard portable form of date arithmetic and works on Postgres, Snowflake, DuckDB, and BigQuery natively; DATE_ADD(ts, INTERVAL 1 DAY) (MySQL, BigQuery) and DATEADD(day, 1, ts) (SQL Server) are function forms; INTERVAL arithmetic clamps to valid dates on month boundaries (Jan 31 + 1 month = Feb 28) but engines differ on edge cases.

Slot 1 — INTERVAL literals.

• INTERVAL '1 day' — one day. Postgres, Snowflake, DuckDB.
• INTERVAL '3 hours 30 minutes' — combined.
• INTERVAL '2 months' — months.
• INTERVAL '1 year 6 months' — combined.
• Multiplication. 3 * INTERVAL '1 day' = INTERVAL '3 days'.
• From integer — MAKE_INTERVAL(days => 5) on Postgres.

Slot 2 — INTERVAL arithmetic.

• ts + INTERVAL 'X' — add interval.
• ts - INTERVAL 'X' — subtract.
• ts1 - ts2 — returns INTERVAL (Postgres).
• ts + INTERVAL '2.5 hours' — fractional intervals.
• Result type. Same as input (TIMESTAMP or DATE).

Slot 3 — DATE_ADD family (MySQL, BigQuery).

• DATE_ADD(date, INTERVAL 1 DAY) — MySQL, BigQuery.
• TIMESTAMPADD(DAY, 1, ts) — BigQuery TIMESTAMP_ADD.
• DATE_SUB(date, INTERVAL 1 DAY) — MySQL sub.
• Handy on MySQL where INTERVAL alone doesn't compile in all contexts.

Slot 4 — DATEADD (SQL Server, Snowflake).

• DATEADD(day, 1, ts) — SQL Server. First arg is the part (day, hour, month, year, quarter).
• DATEDIFF(day, start, end) — SQL Server. Returns integer difference.
• DATEDIFF() semantics vary. Postgres has no DATEDIFF; MySQL DATEDIFF returns days between; BigQuery TIMESTAMP_DIFF/DATE_DIFF vary.

Slot 5 — Snowflake DATEADD.

• DATEADD(DAY, 1, ts) — same as SQL Server.
• DATEDIFF(DAY, a, b) — same.
• Snowflake supports both INTERVAL and DATEADD.

Slot 6 — the month-boundary problem.

• '2026-01-31'::DATE + INTERVAL '1 month' — Postgres returns 2026-02-28. Clamped.
• '2026-02-28' + INTERVAL '1 year' — 2027-02-28. Standard.
• '2024-02-29' + INTERVAL '1 year' — 2025-02-28. Leap edge, clamped.
• '2024-02-29' + INTERVAL '4 years' — 2028-02-29. Back to leap.
• Some engines error on impossible dates; Postgres always clamps.

Slot 7 — DATE arithmetic vs TIMESTAMP arithmetic.

• DATE - DATE. Returns INTEGER (days) in Postgres, MySQL, BigQuery.
• DATE - INTERVAL. Returns DATE (or TIMESTAMP if interval has time part).
• TIMESTAMP - TIMESTAMP. Returns INTERVAL in Postgres; different in other engines.
• Consistent — always be explicit about which unit you want.

Slot 8 — engine dialect for common ops.

Op	Postgres	MySQL	SQL Server	BigQuery	Snowflake
ts + 1 day	ts + INTERVAL '1 day'	DATE_ADD(ts, INTERVAL 1 DAY)	DATEADD(DAY, 1, ts)	TIMESTAMP_ADD(ts, INTERVAL 1 DAY)	DATEADD(DAY, 1, ts)
ts + 1 month	ts + INTERVAL '1 month'	DATE_ADD(ts, INTERVAL 1 MONTH)	DATEADD(MONTH, 1, ts)	TIMESTAMP_ADD(ts, INTERVAL 1 MONTH) (BigQuery may reject; use DATE_ADD)	DATEADD(MONTH, 1, ts)
days between	end - start	DATEDIFF(end, start)	DATEDIFF(day, start, end)	DATE_DIFF(end, start, DAY)	DATEDIFF(DAY, start, end)

Slot 9 — cumulative interval on rolling windows.

• WINDOW OVER (ORDER BY ts RANGE INTERVAL '7 days' PRECEDING) — rolling 7-day window in window functions.
• Postgres native. Snowflake, BigQuery too.
• MySQL — since 8.0. Older versions require workarounds.

Slot 10 — days in month, quarter start, end of month.

• DATE_TRUNC('month', ts) + INTERVAL '1 month' - INTERVAL '1 day' — end of month.
• EXTRACT(DAY FROM (DATE_TRUNC('month', ts) + INTERVAL '1 month' - INTERVAL '1 day')) — days in month.
• Quarter start. DATE_TRUNC('quarter', ts).

Common beginner mistakes

• Using + INTERVAL 1 DAY (missing quotes) on Postgres — error.
• Using DATE_ADD on Postgres — doesn't exist.
• Assuming month arithmetic is exact — clamped on edge.
• Confusing DATEDIFF arg order across engines.
• Using INTERVAL '30 days' when you meant INTERVAL '1 month' — 30 days ≠ 1 month.

Worked example — computing "days since"

Code.

-- Postgres
SELECT id, event_at, CURRENT_DATE - event_at::DATE AS days_since
FROM events;

-- MySQL
SELECT id, event_at, DATEDIFF(CURRENT_DATE, event_at) AS days_since
FROM events;

-- SQL Server
SELECT id, event_at, DATEDIFF(DAY, event_at, GETDATE()) AS days_since
FROM events;

-- BigQuery
SELECT id, event_at, DATE_DIFF(CURRENT_DATE(), DATE(event_at), DAY) AS days_since
FROM events;

-- Snowflake
SELECT id, event_at, DATEDIFF(DAY, event_at, CURRENT_DATE) AS days_since
FROM events;

Rule of thumb. Every engine has "days since" but the syntax varies. Memorize your engine's DATEDIFF.

Worked example — end-of-month

Code.

-- Postgres
SELECT DATE_TRUNC('month', DATE '2026-07-12') + INTERVAL '1 month' - INTERVAL '1 day' AS eom;
-- 2026-07-31

-- MySQL
SELECT LAST_DAY('2026-07-12') AS eom;
-- 2026-07-31

-- SQL Server
SELECT EOMONTH('2026-07-12') AS eom;
-- 2026-07-31

-- BigQuery
SELECT LAST_DAY(DATE '2026-07-12') AS eom;

-- Snowflake
SELECT LAST_DAY(DATE '2026-07-12') AS eom;

Rule of thumb. Each engine has a native "last day of month" function. Know yours.

sql intervals interview question on rolling window

A senior interviewer asks: "Compute 7-day rolling average revenue per day. Use INTERVAL for portability where possible."

Solution Using window function with RANGE INTERVAL

SELECT
  DATE(event_at) AS day,
  SUM(revenue) AS daily_revenue,
  AVG(SUM(revenue)) OVER (
    ORDER BY DATE(event_at)
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS revenue_7day_avg
FROM events
GROUP BY DATE(event_at)
ORDER BY day;

Step-by-step trace.

Day	Daily	7-day avg
07-01	100	100
07-02	120	110
07-03	90	103.3
07-04	110	105
...	...	...
07-08	130	108.6

Output: Rolling avg per day.

Why this works — concept by concept:

• Aggregate to daily first — SUM by DATE.
• Window with RANGE INTERVAL '6 days' PRECEDING — 7-day rolling window including current day.
• AVG over window — rolling average.
• Portable RANGE syntax — works on Postgres, Snowflake, BigQuery.

SQL
Topic — window functions
Window function drills

Practice →

SQL Topic — aggregation SQL aggregation drills

Practice →

---

4. Truncation + DATE_TRUNC + half-open windows

date_trunc and half-open time window — bucketing timestamps to hour/day/month + the canonical [start, end) range pattern

The mental model in one line: DATE_TRUNC(unit, ts) collapses a timestamp to the start of the specified unit (month, day, hour), which is exactly the primitive every dashboard uses for time-series aggregation; and the **half-open WHERE ts >= start AND ts < end window pattern is the correct way to filter a range because it avoids the boundary bug that BETWEEN produces on fractional-second timestamps**.

Slot 1 — DATE_TRUNC anatomy.

• DATE_TRUNC('unit', ts) — returns ts with everything below the unit set to 0/first.
• Unit values. microsecond, millisecond, second, minute, hour, day, week (ISO), month, quarter, year, decade, century, millennium.
• Result — same type as input (TIMESTAMP → TIMESTAMP; TIMESTAMPTZ → TIMESTAMPTZ).
• Postgres, Snowflake, DuckDB, BigQuery — native.
• MySQL — no direct equivalent. Use DATE_FORMAT(ts, '%Y-%m-01') or compose.

Slot 2 — DATE_TRUNC examples.

• DATE_TRUNC('month', '2026-07-15 14:32') = '2026-07-01 00:00:00'.
• DATE_TRUNC('day', '2026-07-15 14:32') = '2026-07-15 00:00:00'.
• DATE_TRUNC('hour', '2026-07-15 14:32:45') = '2026-07-15 14:00:00'.
• DATE_TRUNC('week', '2026-07-15') — ISO week; returns start of Monday.
• DATE_TRUNC('quarter', '2026-07-15') = '2026-07-01'.
• DATE_TRUNC('year', '2026-07-15') = '2026-01-01'.

Slot 3 — the half-open window.

• WHERE ts >= '2026-07-01' AND ts < '2026-08-01' — includes all of July, excludes 2026-08-01.
• Notation — [2026-07-01, 2026-08-01) — bracket = inclusive; parenthesis = exclusive.
• Why prefer half-open.
  • No fractional-second boundary bug.
  • Composable — end of one window = start of next.
  • Sortable — end < next start naturally.
  • No overlap.
• BETWEEN alternative. BETWEEN '2026-07-01' AND '2026-07-31 23:59:59.999999' — messy, engine-dependent precision.

Slot 4 — bucketing patterns.

• GROUP BY DATE_TRUNC('day', event_at) — one bucket per day.
• GROUP BY DATE_TRUNC('month', event_at) — one bucket per month.
• GROUP BY DATE_TRUNC('hour', event_at) — hourly.
• GROUP BY DATE_TRUNC('week', event_at) — ISO week (Mon-Sun).
• Bucketing to hour of day — GROUP BY EXTRACT(HOUR FROM event_at) — 0-23.

Slot 5 — DATE_TRUNC with TZ.

• DATE_TRUNC('day', event_at) on TIMESTAMPTZ — truncates in session TZ.
• DATE_TRUNC('day', event_at AT TIME ZONE 'America/Los_Angeles') — LA-local day.
• Practical — for LA-local daily rollup, convert first then truncate.

Slot 6 — the "last N days" filter.

• WHERE ts >= NOW() - INTERVAL '7 days' — rolling 7 days.
• WHERE ts >= CURRENT_DATE - INTERVAL '7 days' — 7 days from today's midnight.
• WHERE ts >= DATE_TRUNC('day', NOW()) - INTERVAL '7 days' — start of 7 days ago, midnight.

Slot 7 — GENERATE_SERIES for continuous date axes.

• Postgres. SELECT generate_series('2026-07-01'::DATE, '2026-07-31'::DATE, INTERVAL '1 day').
• BigQuery. GENERATE_DATE_ARRAY('2026-07-01', '2026-07-31', INTERVAL 1 DAY).
• Snowflake. No direct equivalent; use recursive CTE or TABLE(GENERATOR) trick.
• Use case. Ensure all days appear in dashboard, even those with 0 events.

Slot 8 — LEFT JOIN with generated dates for zero-day.

WITH date_axis AS (
  SELECT generate_series('2026-07-01'::DATE, '2026-07-31'::DATE, INTERVAL '1 day')::DATE AS day
)
SELECT
  d.day,
  COALESCE(SUM(e.revenue), 0) AS revenue
FROM date_axis d
LEFT JOIN events e ON DATE_TRUNC('day', e.event_at) = d.day
GROUP BY d.day
ORDER BY d.day;

Slot 9 — DATE_TRUNC + half-open combined.

-- July 2026 monthly rollup
WITH july_events AS (
  SELECT * FROM events
  WHERE event_at >= '2026-07-01' AND event_at < '2026-08-01'
)
SELECT DATE_TRUNC('day', event_at) AS day, SUM(revenue)
FROM july_events
GROUP BY DATE_TRUNC('day', event_at)
ORDER BY day;

• Filter first with half-open. Bucket with DATE_TRUNC.

Slot 10 — MySQL DATE_TRUNC alternatives.

• STR_TO_DATE(DATE_FORMAT(ts, '%Y-%m-01'), '%Y-%m-%d') — start of month.
• STR_TO_DATE(DATE_FORMAT(ts, '%Y-%m-%d'), '%Y-%m-%d') — start of day.
• MySQL 8.0.27+ has DATE_TRUNC semi-native. Check version.

Common beginner mistakes

• Using BETWEEN on TIMESTAMP.
• Using DATE(ts) when meaning DATE_TRUNC('day', ts) — same on Postgres but different on some engines with TZ handling.
• Grouping by fractional-second timestamps — never buckets.
• Not COALESCE-ing zero-day windows.
• Assuming DATE_TRUNC works on all engines uniformly.

Worked example — the daily active users rollup

Code.

SELECT
  DATE_TRUNC('day', event_at) AS day,
  COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_at >= '2026-07-01' AND event_at < '2026-08-01'
GROUP BY DATE_TRUNC('day', event_at)
ORDER BY day;

Step-by-step explanation.

1. Half-open filter for July.
2. DATE_TRUNC to day.
3. COUNT DISTINCT user for daily active users.
4. Sort by day.

Output. DAU per day for July.

Rule of thumb. DATE_TRUNC + half-open = canonical time-series rollup.

Worked example — filling in zero-days

Code.

WITH date_axis AS (
  SELECT generate_series('2026-07-01'::DATE, '2026-07-31'::DATE, INTERVAL '1 day')::DATE AS day
)
SELECT
  d.day,
  COALESCE(COUNT(DISTINCT e.user_id), 0) AS dau
FROM date_axis d
LEFT JOIN events e ON DATE_TRUNC('day', e.event_at)::DATE = d.day
GROUP BY d.day
ORDER BY d.day;

Output. Every day of July, even zero-DAU days.

Rule of thumb. LEFT JOIN with a generated axis fills zero-buckets.

Worked example — DATE_TRUNC with TZ conversion

Code.

-- LA-local daily buckets
SELECT
  DATE_TRUNC('day', event_at AT TIME ZONE 'America/Los_Angeles')::DATE AS la_day,
  SUM(revenue) AS revenue
FROM events
WHERE event_at >= (TIMESTAMP '2026-07-01' AT TIME ZONE 'America/Los_Angeles')
  AND event_at <  (TIMESTAMP '2026-08-01' AT TIME ZONE 'America/Los_Angeles')
GROUP BY 1
ORDER BY 1;

Step-by-step explanation.

1. Convert to LA TZ; truncate to day; cast to DATE.
2. Half-open filter uses LA-local literals converted to UTC via AT TIME ZONE.
3. GROUP BY the LA day.

Output. LA-local daily revenue.

Rule of thumb. For localized reports, convert to local TZ, then truncate.

date_trunc interview question on multi-grain rollup

A senior interviewer asks: "Build a report with three tabs — Daily, Weekly, Monthly — all from the same base table. Use DATE_TRUNC and reuse."

Solution Using GROUPING SETS + DATE_TRUNC for multi-grain in one query

SELECT
  DATE_TRUNC('day', event_at)   AS day,
  DATE_TRUNC('week', event_at)  AS week,
  DATE_TRUNC('month', event_at) AS month,
  SUM(revenue) AS revenue,
  GROUPING_ID(
    DATE_TRUNC('day', event_at),
    DATE_TRUNC('week', event_at),
    DATE_TRUNC('month', event_at)
  ) AS grouping_id
FROM events
WHERE event_at >= '2026-07-01' AND event_at < '2026-08-01'
GROUP BY GROUPING SETS (
  (DATE_TRUNC('day', event_at)),
  (DATE_TRUNC('week', event_at)),
  (DATE_TRUNC('month', event_at))
);

Step-by-step trace.

Three grouping sets: day, week, month. Single scan. Client filters by grouping_id.

Output: All three grains in one query.

Why this works — concept by concept:

• DATE_TRUNC per grain — flexible bucketing.
• GROUPING SETS for multi-grain in one scan — from Blog262.
• grouping_id for client dispatch — client picks day/week/month.
• Half-open filter — no boundary bug.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

SQL Topic — SQL SQL practice library

Practice →

---

5. Dialect matrix + patterns

8-engine dialect matrix + patterns for portable time-series SQL

The mental model in one line: date/time is one of the most fragmented surface areas in SQL — every engine has different function names for the same operations, and the safe strategy is to (1) know the ANSI INTERVAL syntax which most warehouses support, (2) know your engine's DATE_TRUNC / DATE_ADD / DATEDIFF equivalents, and (3) always store UTC and use half-open windows for maximum portability.

Slot 1 — the 8-engine matrix.

Op	Postgres	MySQL 8	SQL Server	Oracle	Snowflake	BigQuery	Databricks	DuckDB
Current TS	NOW() / CURRENT_TIMESTAMP	NOW()	GETDATE()	SYSDATE / SYSTIMESTAMP	CURRENT_TIMESTAMP()	CURRENT_TIMESTAMP()	CURRENT_TIMESTAMP()	NOW()
Current DATE	CURRENT_DATE	CURDATE()	CAST(GETDATE() AS DATE)	TRUNC(SYSDATE)	CURRENT_DATE()	CURRENT_DATE()	CURRENT_DATE()	CURRENT_DATE
INTERVAL	native	INTERVAL 1 DAY	DATEADD	INTERVAL / NUMTODSINTERVAL	INTERVAL	INTERVAL	INTERVAL	INTERVAL
DATE_TRUNC	native	limited	DATETRUNC (2022+)	TRUNC	native	native	date_trunc	native
DATE_ADD	ts + INTERVAL	DATE_ADD()	DATEADD()	ts + N	DATEADD()	DATE_ADD()	date_add()	ts + INTERVAL
AT TIME ZONE	native	CONVERT_TZ()	AT TIME ZONE	FROM_TZ / AT TIME ZONE	CONVERT_TIMEZONE()	TIMESTAMP()	from_utc_timestamp()	AT TIME ZONE
EXTRACT	native	native	DATEPART	native	native	EXTRACT	EXTRACT	EXTRACT

Slot 2 — formatting.

• Postgres, Oracle, Snowflake. TO_CHAR(ts, 'YYYY-MM-DD').
• MySQL. DATE_FORMAT(ts, '%Y-%m-%d').
• SQL Server. FORMAT(ts, 'yyyy-MM-dd') or CONVERT(VARCHAR, ts, 23).
• BigQuery. FORMAT_DATE('%Y-%m-%d', d) / FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', ts).
• Databricks. date_format(ts, 'yyyy-MM-dd').
• DuckDB. strftime(ts, '%Y-%m-%d').

Slot 3 — parsing.

• Postgres. TO_TIMESTAMP('2026-07-12', 'YYYY-MM-DD').
• MySQL. STR_TO_DATE('2026-07-12', '%Y-%m-%d').
• SQL Server. PARSE('2026-07-12' AS DATE USING 'en-US') or CAST.
• BigQuery. PARSE_DATE('%Y-%m-%d', '2026-07-12').

Slot 4 — Unix epoch.

• EXTRACT(EPOCH FROM ts) — Postgres, MySQL 8+, DuckDB.
• UNIX_TIMESTAMP(ts) — MySQL.
• DATEDIFF(SECOND, '1970-01-01', ts) — SQL Server.
• UNIX_TIMESTAMP(ts) — BigQuery.
• DATE_PART('epoch_second', ts) — Snowflake.

Slot 5 — day of week.

• EXTRACT(DOW FROM ts) — Postgres, 0-6, Sunday-Saturday.
• DAYOFWEEK(ts) — MySQL, 1-7, Sunday-Saturday.
• DATEPART(WEEKDAY, ts) — SQL Server.
• EXTRACT(DAYOFWEEK FROM ts) — BigQuery.
• ISO — use ISODOW. EXTRACT(ISODOW FROM ts) — 1-7, Monday-Sunday.

Slot 6 — ISO week.

• EXTRACT(WEEK FROM ts) or DATE_PART('week', ts).
• ISO — where Monday is start of week and week 1 contains Jan 4.

Slot 7 — timestamp arithmetic on epoch.

• TO_TIMESTAMP(epoch_seconds) — Postgres.
• FROM_UNIXTIME(seconds) — MySQL.
• DATEADD(SECOND, seconds, '1970-01-01') — SQL Server.
• TIMESTAMP_SECONDS(seconds) — BigQuery.

Slot 8 — common recipe: last N days revenue.

-- Postgres
SELECT * FROM events WHERE event_at >= NOW() - INTERVAL '7 days';

-- MySQL 8
SELECT * FROM events WHERE event_at >= NOW() - INTERVAL 7 DAY;

-- SQL Server
SELECT * FROM events WHERE event_at >= DATEADD(day, -7, GETDATE());

-- BigQuery
SELECT * FROM events WHERE event_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY);

-- Snowflake
SELECT * FROM events WHERE event_at >= DATEADD(DAY, -7, CURRENT_TIMESTAMP());

Slot 9 — portable patterns.

• UTC-first storage. Universal.
• Half-open windows. >= and <. Universal.
• INTERVAL syntax where supported. ANSI standard on Postgres, Snowflake, DuckDB, MySQL, BigQuery.
• DATE_TRUNC on modern warehouses. Postgres, Snowflake, BigQuery, DuckDB.

Slot 10 — the golden five.

• Store UTC.
• Half-open windows.
• DATE_TRUNC for bucketing.
• AT TIME ZONE for display.
• INTERVAL for arithmetic.

Cheat sheet — date & time recipe list

• Store UTC. TIMESTAMPTZ on Postgres; equivalent per engine.
• Convert on read. AT TIME ZONE 'X'.
• Half-open windows. WHERE ts >= start AND ts < end. Never BETWEEN.
• DATE_TRUNC for bucketing. DATE_TRUNC('month', ts) = start of month.
• INTERVAL for portability. ts + INTERVAL '1 day'.
• DATE_ADD / DATEADD for engine-specific.
• EXTRACT for components. EXTRACT(YEAR FROM ts).
• NOW() / CURRENT_TIMESTAMP. Constant per tx (Postgres).
• CURRENT_DATE. Today's date in session TZ.
• DST. Always store UTC; DST is a display concern.
• Month-boundary clamp. Jan 31 + 1 month = Feb 28. Test edge.
• Leap year edge. Feb 29 + 1 year = Feb 28.
• DATEDIFF varies. SQL Server DATEDIFF(unit, start, end); MySQL DATEDIFF(end, start).
• ISO week. EXTRACT(ISOWEEK FROM ts) on Postgres.
• Day of week ISO. Monday = 1, Sunday = 7.
• Unix epoch. EXTRACT(EPOCH FROM ts).
• From epoch. TO_TIMESTAMP(seconds).
• End of month. DATE_TRUNC('month', ts) + INTERVAL '1 month' - INTERVAL '1 day'.
• Last day of month native. LAST_DAY(ts) on MySQL, BigQuery, Snowflake, Oracle. EOMONTH() on SQL Server.
• generate_series for continuous date axis. Postgres. GENERATE_DATE_ARRAY on BigQuery.
• LEFT JOIN with axis for zero-day gaps.
• Session TZ — always set explicit in app connections.
• DST times — spring forward creates gap; fall back creates overlap. UTC has neither.
• AT TIME ZONE on TZ-aware. UTC → local (returns TS WITHOUT).
• AT TIME ZONE on TZ-naive. Interpret as local, return UTC.
• ::DATE cast to date. Portable across most engines.
• Rolling windows. RANGE INTERVAL '7 days' PRECEDING in window functions.

Frequently asked questions

Should I use TIMESTAMP WITH or WITHOUT TIME ZONE?

Almost always WITH TIME ZONE. WITH TIME ZONE (TIMESTAMPTZ on Postgres, TIMESTAMP_TZ on Snowflake, TIMESTAMP on BigQuery which is always UTC) stores the timestamp as UTC internally regardless of input zone, and converts to session TZ on read. This gives unambiguous absolute moments in time — the same value means the same instant everywhere. WITHOUT TIME ZONE stores wall-clock digits with no zone metadata; the same value means different absolute times in different sessions. Use WITHOUT only for wall-clock intent cases — office hours, holiday dates, business calendar, appointment scheduling in a specific TZ (paired with a separate TZ column). For any moment-in-time data (events, transactions, log entries), always use WITH TIME ZONE. This is the single biggest cause of "time is off" bugs in production databases.

Why does BETWEEN miss my events on the last day?

Because date literals are treated as midnight (00:00:00). WHERE ts BETWEEN '2026-07-01' AND '2026-07-31' translates to ts >= '2026-07-01 00:00:00' AND ts <= '2026-07-31 00:00:00' — this misses any timestamp on July 31 after midnight, including 12:00, 18:00, and 23:59. The fix is the half-open window pattern: WHERE ts >= '2026-07-01' AND ts < '2026-08-01'. This includes all of July (start ≤ July 1 ≤ ... ≤ July 31 23:59:59 < Aug 1) and excludes Aug 1 exactly at midnight. Half-open is portable, precise, and composable — end of one window is the start of the next. Never use BETWEEN for timestamp ranges; use >= AND <.

What's DATE_TRUNC and when should I use it?

DATE_TRUNC(unit, ts) collapses a timestamp to the start of the specified unit — DATE_TRUNC('month', '2026-07-15 14:32') = '2026-07-01 00:00:00'. Common units: microsecond, second, minute, hour, day, week, month, quarter, year. It's the canonical primitive for time-series bucketing — GROUP BY DATE_TRUNC('day', event_at) gives you one row per day. Compared to DATE(ts) which extracts just the date (loses time), DATE_TRUNC preserves the timestamp type. Native on Postgres, Snowflake, BigQuery, Databricks, DuckDB. MySQL requires workarounds via DATE_FORMAT or the newer 8.0.27+ DATE_TRUNC. SQL Server 2022 added DATETRUNC. Rule of thumb — always use DATE_TRUNC for bucketing; never truncate manually via string manipulation.

How do I handle DST transitions?

Store UTC. DST is a display concern only. In UTC, there are no DST transitions — time flows linearly. Convert to local TZ only when displaying to users. If you must handle local wall-clock intent (e.g., "9 AM local time every day"), separate the wall-clock value from the TZ into two columns: scheduled_local TIMESTAMP and scheduled_tz TEXT. Resolve to absolute UTC only when you need to fire the event, using scheduled_local AT TIME ZONE scheduled_tz. During spring forward, the local time 2:30 AM doesn't exist — many engines error or shift to 3:30. During fall back, 1:30 AM happens twice — the first occurrence is picked by default; you can't reliably distinguish them. Never compare local timestamps across DST boundaries without converting to UTC first. Rule of thumb — UTC storage + local display = DST-safe.

What's the difference between INTERVAL and DATE_ADD?

INTERVAL is the ANSI SQL standard syntax — ts + INTERVAL '1 day'. Supported natively on Postgres, Snowflake, BigQuery, DuckDB, MySQL 8+, Databricks. DATE_ADD / DATEADD / TIMESTAMPADD are function forms with engine-specific spellings — DATE_ADD(ts, INTERVAL 1 DAY) on MySQL/BigQuery, DATEADD(DAY, 1, ts) on SQL Server/Snowflake. Both produce the same result on simple cases; INTERVAL is more portable. Both clamp on month boundaries — '2026-01-31' + INTERVAL '1 month' = '2026-02-28' on Postgres. Some engines behave differently on invalid dates; test your engine. For portability, prefer INTERVAL where supported. For legacy compatibility (older MySQL, SQL Server), use the function forms.

How do I compute rolling windows over time?

Two patterns. Grain-based rolling — GROUP BY DATE_TRUNC('day', ts) gives you daily buckets, then use window functions across those buckets: AVG(daily_revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives 7-day rolling average. Time-based rolling — AVG(revenue) OVER (ORDER BY event_at RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) — window slides continuously by time. RANGE INTERVAL requires the ORDER BY column to be sortable by time and typically works on Postgres, Snowflake, BigQuery, DuckDB. Grain-based is more portable and often what dashboards need. For cumulative sums use SUM(revenue) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING). Rule of thumb — bucket first, then use ROWS PRECEDING for stability. Use RANGE INTERVAL only when you need irregular time-based windows.

Practice on PipeCode

• Drill the SQL practice library → — 450+ DE-focused questions covering date/time, INTERVAL, DATE_TRUNC, and time-zone handling.
• Sharpen SQL aggregation drills → for time-series bucketing.
• Layer window function drills → — rolling averages and cumulative sums.
• Warm up with SQL optimization drills → — reading time-range query plans.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql date time intervals` recipe above ships with hands-on practice rooms where you set `SET TIMEZONE = 'UTC'` on a live Postgres, migrate a BETWEEN filter to the half-open `>= AND <` window, benchmark `DATE_TRUNC('month', ts)` for monthly rollup, chain `event_at AT TIME ZONE 'America/Los_Angeles'` for LA-local dashboards, handle the DST spring-forward gap by staying in UTC, and finally build the 7-day rolling revenue window with `RANGE INTERVAL '7 days' PRECEDING` — the exact time-series SQL fluency that senior DE interviews probe. PipeCode pairs every date/time concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `timestamp with time zone` / `date_trunc` / `half-open window` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Aggregation drills →