IntermediateSQL · 25 min

Date Arithmetic: Intermediate

Past the toolkit, date arithmetic stops being syntax and becomes the seam where production reporting most often breaks. The interviewer assumes you can write DATE_TRUNC and INTERVAL arithmetic; what they want to see is whether you've handled the parts of real reporting where the textbook examples break. Timezone-aware aggregation. Fiscal calendars where Q1 starts in April. SLA timing that has to exclude weekends and holidays. Event-time vs ingestion-time analytics in a streaming pipeline. These are where production reporting bugs live, and the interviewer is reading you for whether you've shipped through one.

Reason about timezone conventions for storage, computation, and display

Adapt the date toolkit to fiscal calendars and business-day windows

Choose between generate_series, recursive CTEs, and calendar tables for date spines

Anticipate the failure modes (DST, leap year, fiscal-year drift) that ship to production

EXTRACT, Fiscal Calendars, and ISO Weeks

Daily Life

Interviews

Spot date arithmetic needs: "last 30 days," "same day last year," "business days only," "fiscal quarter."

The question that sets the tone: 'compute monthly revenue per region for our fiscal year, where Q1 starts in April, the warehouse stores transactions in UTC, and the consumer wants the report in the region's local timezone.' The candidate who reaches for DATE_TRUNC('month', txn_ts) and stops there is producing a report whose monthly boundaries are wrong for every region except UTC. The candidate who pre-converts to each region's timezone, then truncates, then aggregates is producing a report that matches what each region's finance team will reconcile against. The mechanical difference is two function calls; the production difference is whether finance ever questions the dashboard.

Where production reporting actually breaks

Four areas catch out every engineer who hasn't yet shipped through them. First: timezone-aware aggregation when the source is UTC and the consumer is local. The fix is the AT TIME ZONE conversion at the right point in the pipeline. Second: fiscal calendars where the company's year does not start on January 1. The fix is a calendar table or arithmetic that subtracts the fiscal-offset months. Third: SLA timing that has to subtract weekends and holidays. The fix is a business-day index. Fourth: event-time vs processing-time in streaming or near-real-time pipelines. The fix is to pick one convention consistently per query. Shipping all four is what separates someone who has lived through these from someone who has only read about them.

UTC source, local-time consumer; conversion happens at a named point in the pipeline.
Q1 starts in April, June, or October; calendar tables hold the per-date mapping.
exclude weekends and holidays; calendar table's is_business_day flag drives the count.
streaming and near-real-time pipelines; pick one convention per query and stay consistent.

You are being tested on production-grade date arithmetic when you hear:

▸"daily revenue, but the consumer is in Tokyo and the data is in UTC"
▸"fiscal year ending June 30; show me Q3 numbers"
▸"hours of customer support response time, business days only"
▸"event time vs server time, which one should drive the report?"
▸"how does daylight saving affect this hourly aggregate?"
▸"the dashboard shows Sunday revenue twice in November; what happened?"

The three timezone conventions

Three conventions for storing and reasoning about timestamps. Store-UTC, compute-UTC, display-local: the canonical convention. Storage is always UTC; aggregations run in UTC; the BI layer or the application converts to local for display. The advantage: arithmetic is unambiguous, DST does not exist, comparisons are exact. The disadvantage: any aggregation that needs local-day boundaries has to convert first, then aggregate. Store-UTC, compute-local: the variant. Storage is UTC; conversion happens at aggregation time so the buckets line up with local days. The advantage: the dashboard's local-time buckets match the consumer's expectations. The disadvantage: queries are more complex and the conversion has to be applied consistently. Store-local: the legacy convention. Storage is whatever local time the writer was in. This is the convention that ships the most bugs and is what most legacy systems are trying to migrate away from.

•When to pre-convert at aggregation time

Dashboard buckets must align with local days/weeks/months
Consumer is a finance or analytics team that reconciles against local-time records
The source is UTC but the report is a per-region report
Example: monthly revenue per region for finance reporting

✓When to keep aggregation in UTC

Report is global; one bucket per UTC day, regardless of consumer geography
The consumer is an engineering or product team thinking in UTC
Multiple regions need to compare on a common axis
Example: hourly system metrics for SRE dashboards

The mental model: storage / computation / display

Memorize this sentence: every timestamp question has three layers. Storage is what the database actually holds (almost always UTC for new systems). Computation is what the query operates on (sometimes UTC, sometimes the consumer's local zone, picked deliberately per query). Display is what the dashboard renders (almost always the consumer's local zone, applied at the BI layer). The question to ask before writing the query is which layer should do the timezone conversion. Wrong-layer conversions are the source of every production timezone bug.

The storage / computation / display mental model:

▸Storage: what the database actually holds (UTC for new systems)
▸Computation: what the query operates on (UTC or local, picked per query)
▸Display: what the dashboard renders (local, at the BI layer)
▸Wrong-layer conversion is the source of every production timezone bug

Timestamps, Timezones, and AT TIME ZONE

Daily Life

Interviews

Write date manipulation using the three core functions and handle dialect differences (DATEADD vs INTERVAL).

Timezone-aware aggregation is the area where code most often graduates from working-for-the-easy-case to surviving-the-edge-cases. The discipline is consistent: storage is UTC, queries are explicit about which zone they aggregate in, conversions happen at named points in the query. Once you write this consistently, the edge-case bugs stop shipping; until you do, they keep showing up in the dashboards finance reconciles against.

The canonical timezone-aware aggregate

	/* Daily revenue per region, with local-time day boundaries */
	WITH local_txns AS (
	SELECT
	region,
	txn_ts AT TIME ZONE region_timezone AS local_ts,
	amount
	FROM transactions AS t
	INNER JOIN regions AS r USING (region)
	WHERE txn_ts >= '2024-01-01' AT TIME ZONE 'UTC'
	AND txn_ts < '2024-02-01' AT TIME ZONE 'UTC'
	)

	SELECT
	region,
	DATE_TRUNC('day', local_ts) AS local_day,
	SUM(amount) AS daily_revenue
	FROM local_txns
	GROUP BY region, DATE_TRUNC('day', local_ts)
	ORDER BY region, local_day

Reading the conversion

The transactions table stores txn_ts as TIMESTAMPTZ in UTC. The regions table has a region_timezone column ('America/Los_Angeles', 'Asia/Tokyo'). The AT TIME ZONE clause converts each transaction's UTC timestamp to the region's local time. DATE_TRUNC then operates on local time, so the day boundaries match the region's calendar. The result: a transaction at 22:00 Pacific (06:00 UTC the next day) groups with Pacific that day, not UTC the next day. The conversion happens in the source CTE; everything downstream operates on the converted local_ts.

Where the conversion can go wrong

Three mistakes ship to production. First: applying AT TIME ZONE to a TIMESTAMP (without zone) instead of a TIMESTAMPTZ. The conversion assumes the timestamp is in UTC; if the column already represents a different timezone, the conversion is wrong. The fix: always know the source column's type and zone before converting. Second: converting at display time instead of aggregation time. DATE_TRUNC in UTC then displaying as local moves UTC buckets to local time but does not re-align the bucket boundaries; the dashboard shows UTC days labeled as local days. The fix: convert before the DATE_TRUNC, not after. Third: hardcoding a single timezone for a multi-region report. A query that uses 'America/Los_Angeles' for every region's local-time aggregate is wrong for Tokyo and London. The fix: parameterize the timezone per region, joining a regions table that holds the convention.

The three timezone-conversion bugs:

▸AT TIME ZONE applied to TIMESTAMP (assumes UTC; wrong if column is not)
▸Conversion at display instead of aggregation (buckets don't realign)
▸Hardcoded single timezone for a multi-region report
▸Each is silent: query runs, numbers look like data, finance reconciles and finds the gap

Daylight saving time

DST creates two specific failure modes. The spring-forward problem: in US Eastern, the local clock skips from 02:00 to 03:00 on the second Sunday in March. A timestamp like '2024-03-10 02:30 US/Eastern' does not exist; engines either throw an error or default to 03:30. The autumn-back problem: in November, the local clock repeats 01:00 to 02:00. A timestamp like '2024-11-03 01:30 US/Eastern' is ambiguous; it occurs twice (once in DST, once not). Aggregations that cross these boundaries can double-count or skip the affected hours. The mitigation is to do all aggregation in UTC and convert only at display, or to use a consistent zone like UTC for any aggregation that crosses DST boundaries.

Spring forward (March): local clock skips 02:00-03:00Timestamp 02:30 US/Eastern does not existAutumn back (November): local clock repeats 01:00-02:00Timestamp 01:30 US/Eastern occurs twice; aggregation double-countsFix: aggregate in UTC across DST boundaries

At Stripe in 2021, the daily merchant payouts query produced a number for one Sunday in November that was off by 4-6% in either direction across different merchants. The cause was DST in the US Eastern zone: the query aggregated in local time, and the hour 01:00-02:00 happened twice, doubling transactions for any merchant in that hour. After investigation, the fix was to switch the aggregation to UTC and convert to local time only for the display layer. The runbook line was 'any query that crosses a DST boundary in the aggregation does so in UTC; local-time aggregation is a CI lint failure for fields used in financial reporting.'

The discipline: convert once, at the right layer

The habit to build is to think about timezone conversion as a layer decision, not a per-line decision. Decide whether this query aggregates in UTC or in local time. Decide where the conversion happens. Write the conversion exactly once, in the source CTE, then use the converted column downstream. Never apply AT TIME ZONE in the middle of a window function or a complex expression; convert first, then operate. The discipline costs one extra CTE and prevents most timezone bugs.

TIP

When the interviewer asks about timezones, name your convention explicitly. 'I'm aggregating in UTC and converting at display' or 'I'm pre-converting to local time in the source CTE and aggregating on the converted column.' Both are valid; picking one and naming it tells the interviewer you've thought about the layer decision.

DST Boundaries and the Missing Hour

Daily Life

Interviews

Pull date parts with EXTRACT, handle fiscal year offsets, and explain ISO week numbering.

Most companies do not align their fiscal year with the calendar year. Apple's fiscal year ends in September; many retailers end in January or February; the US government ends in September. A query that uses DATE_TRUNC('year', date) for fiscal reporting is wrong for any company except those with calendar-year fiscal alignment. Knowing the patterns for fiscal calendars and business-day windows before the interviewer asks is what tells the room you've reported against a real company's books.

Fiscal year arithmetic

Two patterns for fiscal-year computation. Pattern one: arithmetic shift. If the fiscal year starts in April, subtract three months from the date before truncating; the result is the fiscal-year-aligned year. txn_date - INTERVAL '3 months' converts the dates, then DATE_TRUNC('year', ...) groups them into fiscal years. Pattern two: calendar table. Store a calendar table with (date, fiscal_year, fiscal_quarter, fiscal_month) precomputed; join the calendar to the fact table and use the calendar's fiscal columns. The arithmetic pattern is faster for one-off queries; the calendar table is the right pattern for any production environment because it makes fiscal logic explicit and auditable.

	SELECT
	DATE_TRUNC('year', txn_date - INTERVAL '3 months') AS fiscal_year_start,
	SUM(amount) AS fiscal_year_revenue
	FROM transactions
	GROUP BY DATE_TRUNC('year', txn_date - INTERVAL '3 months') ;


	SELECT
	cal.fiscal_year,
	cal.fiscal_quarter,
	SUM(t.amount) AS revenue
	FROM transactions t
	JOIN dim_calendar cal
	ON cal.date = t.txn_date
	GROUP BY cal.fiscal_year, cal.fiscal_quarter
	ORDER BY cal.fiscal_year, cal.fiscal_quarter ;

When the arithmetic shift breaks

The arithmetic pattern works for fiscal years that start on the first of a month. It breaks for fiscal years that start on a non-first-of-month date (some retailers start on the first Sunday of February), for fiscal years with a 4-4-5 week pattern (most retailers), or for any business with a custom fiscal calendar. The calendar table handles all of these because the fiscal columns are precomputed for each calendar date. State this when writing the arithmetic version: 'this works because our fiscal year starts April 1; for non-first-of-month or 4-4-5 calendars I would switch to a calendar table.'

Business-day arithmetic

SLA reporting and operational metrics often need 'business days only': exclude weekends, exclude holidays. Subtracting one date from another gives calendar days; for business days, the calculation needs the calendar table. The pattern: count the rows in dim_calendar where date is between start and end and is_business_day is TRUE. The calendar table holds the is_business_day flag, accounting for weekends and holidays per region or per company.

	/* Business days between submission and resolution */
	SELECT
	ticket_id,
	submitted_at,
	resolved_at,
	(
	SELECT
	COUNT(*)
	FROM dim_calendar AS cal
	WHERE cal.date >= CAST(submitted_at AS DATE)
	AND cal.date < CAST(resolved_at AS DATE)
	AND cal.is_business_day = TRUE
	) AS business_days_to_resolve
	FROM support_tickets

Calendar tables in production

A production calendar table typically has these columns per day: date (PK), day_of_week, week_of_year, month_of_year, quarter, year, fiscal_year, fiscal_quarter, fiscal_month, is_weekend, is_business_day, is_holiday, holiday_name. The table is precomputed for a date range (often 1900-2100 or 1970-2070) and refreshed when holidays for the next year are confirmed. Every fiscal, business-day, or holiday-aware query joins to this table; the join is a fast index lookup. State this when designing a fiscal calendar pipeline: 'dim_calendar is the canonical source for fiscal logic; queries join to it rather than implementing fiscal arithmetic inline.'

date (PK), day_of_week, week_of_year, month_of_year, quarter, year
fiscal_year, fiscal_quarter, fiscal_month, fiscal_week (per company's fiscal convention)
is_weekend, is_business_day, is_holiday, holiday_name (per region or per company)
precomputed for a wide range (1970-2070); refresh when next year's holidays are confirmed

•Arithmetic shift

Works for first-of-month fiscal years
No table dependency; query is self-contained
Right tool for one-off ad-hoc queries
Wrong tool for production reporting; fiscal-year edge cases break it

✓Calendar table

Handles any fiscal calendar shape (4-4-5, custom, multi-fiscal)
Holiday and business-day logic is in one place
Right tool for production reporting and audit
Requires the table to be maintained; new holidays added per year

Edge cases the calendar table fixes

Three edge cases that the arithmetic pattern handles poorly and the calendar table handles cleanly. Leap years: fiscal year arithmetic can produce off-by-one bugs on February 29. Holidays specific to a region: a calendar table with region-aware columns handles regional holidays without per-query logic. Half-day holidays: some companies count Christmas Eve as a half business day; the calendar table can model fractional business days. The calendar table absorbs the complexity once; queries stay simple.

Aligning to Week-Start and Month-End

Daily Life

Interviews

Convert between UTC and local time, explain why comparing timestamps across zones requires explicit conversion.

A date spine is a complete enumeration of dates over a window, used to LEFT JOIN actuals onto so missing dates appear as explicit zero rows. Every period-over-period query, every retention curve, every monitoring metric uses a date spine. The question is which tool to use: generate_series (Postgres-specific, simplest), a recursive CTE (portable across engines), or a calendar table (production-grade). Each has a cost and a use case.

generate_series for Postgres-style spines

Postgres's generate_series is the cleanest way to produce a date spine when you can use it. It takes a start, an end, and an interval; it returns one row per step. Snowflake and BigQuery have similar functions (GENERATOR, GENERATE_DATE_ARRAY) with different signatures. The pattern is engine-specific; the candidate names the engine and uses the appropriate function.

Date spine tools, by engine and use case:

▸Postgres: generate_series (one line, native)
▸BigQuery: GENERATE_DATE_ARRAY + UNNEST
▸Snowflake: GENERATOR + DATEADD arithmetic
▸Portable: recursive CTE (5-7 lines, works on every modern engine)
▸Production: calendar table (free fiscal and business-day metadata)

	SELECT
	day :: DATE
	FROM generate_series('2024-01-01' :: DATE, '2024-12-31' :: DATE, INTERVAL '1 day') AS day ;


	SELECT
	day
	FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', '2024-12-31', INTERVAL 1 DAY)) AS day ;


	SELECT
	DATEADD('day', SEQ4(), '2024-01-01' :: DATE) AS day
	FROM TABLE(GENERATOR(ROWCOUNT = > 366))
	WHERE DATEADD('day', SEQ4(), '2024-01-01' :: DATE) <= '2024-12-31' :: DATE ;

Recursive CTE for portability

Recursive CTEs work on every engine that supports them (Postgres, MySQL 8+, BigQuery, Snowflake, SQL Server). The recursive shape is the same: anchor on the start date, increment by one day per iteration, terminate when the date exceeds the end. The query is more verbose than generate_series but ships across engines without modification. For codebases that target multiple engines, recursive CTE is the safe default.

	/* Recursive CTE date spine, portable */
	WITH RECURSIVE date_spine AS (
	SELECT
	DATE '2024-01-01' AS day

	UNION ALL

	SELECT
	day + INTERVAL '1 day'
	FROM date_spine
	WHERE day < DATE '2024-12-31'
	)

	SELECT
	day
	FROM date_spine

Calendar table for production reporting

A calendar table joined as the spine is the right pattern for production reporting. The calendar already exists for fiscal and business-day logic; using it as the spine gives free fiscal-year columns, free is_business_day flags, free holiday awareness. The query is the simplest of the three: SELECT FROM dim_calendar WHERE date BETWEEN start AND end. The cost is the table dependency; the benefit is that fiscal and business-day logic is automatically integrated.

	/* Calendar-table spine, production-grade */
	SELECT
	cal.date AS day
	FROM dim_calendar AS cal
	WHERE cal.date >= '2024-01-01'
	AND cal.date < '2025-01-01'
	AND cal.is_business_day = /* Optional: filter to business days only */ TRUE

Picking between the three

For one-off ad-hoc queries: generate_series if the engine supports it; recursive CTE if not. For production reporting that involves fiscal or business-day logic: calendar table. For multi-engine codebases: recursive CTE as the lowest common denominator. The choice is a workload question, not a personal preference. State the choice when writing: 'generate_series here because this is a one-off on Postgres; in production I would join to dim_calendar so the spine inherits fiscal and business-day columns.'

•generate_series

Simplest syntax; one line for a date range
Engine-specific; not portable
Right for ad-hoc Postgres queries
No fiscal or business-day metadata

✓Recursive CTE

Portable across engines that support recursive CTE
More verbose; 5-7 lines for the same range
Right for multi-engine codebases
No fiscal or business-day metadata

The spine joined to actuals

The spine is rarely the final query; it is the LEFT side of a join to actuals. SELECT spine.day, COALESCE(SUM(actuals.amount), 0) AS revenue FROM spine LEFT JOIN actuals ON actuals.date = spine.day GROUP BY spine.day. The result is one row per day in the window, with zero for missing dates. This is what makes period-over-period queries safe against gaps and what makes retention curves include cohorts with no activity. The spine plus LEFT JOIN is the canonical pattern; the spine alone is rarely useful.

On a date spine for a multi-dimensional query (region × day, product × day), the spine becomes a cross-join of dimensions. Generate the spine, CROSS JOIN with the distinct dimensions, LEFT JOIN actuals. The cardinality grows multiplicatively; for production reporting, restrict to dimension combinations that have ever been populated, not the full cross product.

Dialect Differences in Date Functions

Daily Life

Interviews

Create a date spine using recursive CTE or GENERATE_SERIES and LEFT JOIN to fill calendar gaps in sparse data.

Past correctness, the interviewer escalates to cost: 'this query is slow; what would you change?' Date arithmetic queries have specific performance levers, and the deeper answer names each one rather than gesturing at 'add an index.' Indexing the date column, partition pruning, function-call rewrites to expose predicates to the optimizer, and the EXPLAIN-then-tune discipline.

Indexing date columns

A WHERE clause on txn_date benefits from an index on txn_date. A WHERE clause on DATE_TRUNC('month', txn_date) does not benefit from a plain index on txn_date; the function call hides the column from the optimizer. The fix: index the expression itself (expression indexes on Postgres) or restructure the query to use a range predicate the optimizer can use.

	SELECT *
	FROM transactions
	WHERE DATE_TRUNC('month', txn_date) = '2024-03-01' ;


	SELECT *
	FROM transactions
	WHERE txn_date >= '2024-03-01' AND txn_date < '2024-04-01' ;

Partition pruning

Most analytical warehouses partition fact tables by date. Snowflake clusters; BigQuery partitions; Iceberg partitions; Hive partitions. Each engine prunes partitions when the WHERE clause matches the partition column. A query that uses DATE_TRUNC('month', txn_date) in the WHERE clause may not benefit from partition pruning because the engine cannot map the function call to the partition definition. A query that uses a date range can. The performance difference is often 10-100x because partition pruning skips entire files instead of reading and filtering. State this when writing: 'I'm using a date range instead of DATE_TRUNC in the WHERE because partition pruning needs a column predicate, not a function call.'

EXPLAIN as the diagnostic

Before optimizing, run EXPLAIN. Look at whether the date column drives an index scan or a sequential scan. If sequential, check whether the WHERE clause hides the column behind a function. If the date column is the partition key, check whether the engine pruned to the expected partitions. EXPLAIN is the diagnostic; the optimization follows from what the diagnostic shows.

•Function-hiding patterns to avoid

WHERE DATE_TRUNC('month', txn_date) = '2024-03-01' (function on column)
WHERE EXTRACT(YEAR FROM txn_date) = 2024 (function on column)
WHERE txn_date::TEXT LIKE '2024-03%' (cast plus pattern match)
Each one hides txn_date from the optimizer; no index use, no partition pruning

✓Range patterns that expose the column

WHERE txn_date >= '2024-03-01' AND txn_date < '2024-04-01'
WHERE txn_date >= '2024-01-01' AND txn_date < '2025-01-01'
Each one is a plain column comparison; the optimizer uses indexes and partitions
Same logical result; orders of magnitude faster on large tables

Window function performance

LAG, LEAD, and window aggregates with date-based ordering can be expensive at scale. The cost is dominated by the partition + sort step. On a billion-row source, sorting by date within each partition is the dominant cost. The mitigation: pre-aggregate to the reporting grain (daily, weekly) before the window function so the window operates on a smaller intermediate. State this when writing: 'I'm aggregating to daily before the LAG; the window function then operates on rows in the thousands per partition instead of millions.'

Story call-back: the fiscal-year question from s0

Recall the question from the introduction: 'compute monthly revenue per region for our fiscal year, where Q1 starts in April, UTC source, local-time consumer.' The full answer composes everything in this lesson. Join to dim_calendar for the fiscal-year and quarter columns. Pre-convert UTC to local time per region in the source CTE. Use a date range in the WHERE clause so partition pruning works. Aggregate on the converted local timestamp. Display the fiscal calendar attributes from dim_calendar. The query is twelve lines; the design choices behind each line are what make it production-grade. A shorter version is mechanically correct for the easy case and silently wrong for half the edge cases. This version is correct because every line answers a specific question about layer, source, conversion, partition, or fiscal mapping.

	/* Full answer composing the design choices from this lesson */
	WITH local_txns AS (
	SELECT
	region,
	txn_ts AT TIME ZONE region_tz AS local_ts,
	amount
	FROM transactions AS t
	INNER JOIN regions AS r USING (region)
	WHERE t.txn_ts >= '2023-04-01' AT TIME ZONE 'UTC'
	AND t.txn_ts < '2024-04-01' AT TIME ZONE 'UTC'
	)

	SELECT
	cal.fiscal_year,
	cal.fiscal_quarter,
	cal.fiscal_month,
	lt.region,
	SUM(lt.amount) AS revenue
	FROM local_txns AS lt
	INNER JOIN dim_calendar AS cal
	ON cal.date = CAST(
	DATE_TRUNC('day', lt.local_ts)
	AS DATE
	)
	GROUP BY cal.fiscal_year, cal.fiscal_quarter, cal.fiscal_month, lt.region
	ORDER BY cal.fiscal_year, cal.fiscal_quarter, cal.fiscal_month, lt.region

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering interview at a multi-region SaaS company. The interviewer asks: 'Compute monthly revenue per region for our fiscal year, where Q1 starts in April. Transactions are in UTC; finance wants local-time month boundaries.'

You frame the three layers: storage UTC, computation local-time per region, display either format. You name the convention before writing.

Source CTE does AT TIME ZONE conversion per region (regions table holds the timezone). Downstream operates on the converted local timestamp.

Fiscal mapping comes from dim_calendar; the join exposes fiscal_year, fiscal_quarter, fiscal_month columns precomputed for every date.

WHERE clause uses an absolute UTC range against txn_ts so partition pruning works; the optimizer reads only the affected partitions.

Follow-up: 'Why dim_calendar instead of arithmetic?' You say: 'Arithmetic shift breaks on 4-4-5 retail calendars, on fiscal years not starting on first-of-month, and on holiday-aware business-day logic. The calendar table absorbs the complexity once; queries stay simple.'

Follow-up: 'The dashboard double-counted one Sunday in November.' You say: 'DST. Local-time aggregation included the repeated 01:00-02:00 hour twice. The fix is to aggregate in UTC and convert only at display, or to use a DST-safe local-time zone like Asia/Tokyo for the test.'

Closing: 'The query has twelve lines and roughly six explicit design choices: timezone layer, calendar table, range predicate, fiscal column source, partition awareness, aggregation order. Each one is the difference between production-grade and a query that ships a quiet bug.'

KEY TAKEAWAYS

Every timestamp question has three layers: storage (UTC for new systems), computation (chosen deliberately per query), and display (the consumer's local zone at the BI layer). Wrong-layer conversion is the source of essentially every production timezone bug.

Convert exactly once, in the source CTE, then operate on the converted column: txn_ts AT TIME ZONE region_timezone AS local_ts before the DATE_TRUNC. Truncating in UTC and relabeling as local moves the numbers without realigning the bucket boundaries.

DST breaks local-time aggregation in two ways: spring forward makes 02:30 US Eastern nonexistent, and fall back makes 01:30 ambiguous because the hour repeats. Aggregating in local time across a fall-back Sunday double counts that hour, which is why the aggregation runs in UTC and the conversion happens at display.

The arithmetic fiscal shift, DATE_TRUNC('year', txn_date - INTERVAL '3 months'), only holds when the fiscal year starts on the first of a month. Non-first-of-month starts, 4-4-5 retail calendars, regional holidays, and half-days all need dim_calendar with precomputed fiscal and is_business_day columns.

Pick the date spine by workload: generate_series for a one-off on Postgres, WITH RECURSIVE for a multi-engine codebase, and a calendar table in production because the spine then inherits fiscal, business-day, and holiday columns for free. The spine is only useful LEFT JOINed to actuals with COALESCE(SUM(amount), 0) so gap days appear as explicit zeros.

Wrapping the date column in a function hides it from the optimizer. Replace WHERE DATE_TRUNC('month', txn_date) = '2024-03-01' with the half-open range txn_date >= '2024-03-01' AND txn_date < '2024-04-01'; partition pruning skips whole files and is routinely a 10x to 100x difference.

Every data engineering question involves dates; most candidates fumble timezone math

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: EXTRACT, Fiscal Calendars, and ISO Weeks, Timestamps, Timezones, and AT TIME ZONE, DST Boundaries and the Missing Hour, Aligning to Week-Start and Month-End, Dialect Differences in Date Functions

Lesson Sections

EXTRACT, Fiscal Calendars, and ISO Weeks (concepts: sqlTimezones)
Where production reporting actually breaks Four areas catch out every engineer who hasn't yet shipped through them. First: timezone-aware aggregation when the source is UTC and the consumer is local. The fix is the AT TIME ZONE conversion at the right point in the pipeline. Second: fiscal calendars where the company's year does not start on January 1. The fix is a calendar table or arithmetic that subtracts the fiscal-offset months. Third: SLA timing that has to subtract weekends and holidays. T
Timestamps, Timezones, and AT TIME ZONE (concepts: sqlTimezones)
Timezone-aware aggregation is the area where code most often graduates from working-for-the-easy-case to surviving-the-edge-cases. The discipline is consistent: storage is UTC, queries are explicit about which zone they aggregate in, conversions happen at named points in the query. Once you write this consistently, the edge-case bugs stop shipping; until you do, they keep showing up in the dashboards finance reconciles against. The canonical timezone-aware aggregate Reading the conversion The tr
DST Boundaries and the Missing Hour (concepts: sqlDateTrunc)
Most companies do not align their fiscal year with the calendar year. Apple's fiscal year ends in September; many retailers end in January or February; the US government ends in September. A query that uses DATE_TRUNC('year', date) for fiscal reporting is wrong for any company except those with calendar-year fiscal alignment. Knowing the patterns for fiscal calendars and business-day windows before the interviewer asks is what tells the room you've reported against a real company's books. Fiscal
Aligning to Week-Start and Month-End (concepts: sqlRecursiveCte)
A date spine is a complete enumeration of dates over a window, used to LEFT JOIN actuals onto so missing dates appear as explicit zero rows. Every period-over-period query, every retention curve, every monitoring metric uses a date spine. The question is which tool to use: generate_series (Postgres-specific, simplest), a recursive CTE (portable across engines), or a calendar table (production-grade). Each has a cost and a use case. generate_series for Postgres-style spines Postgres's generate_se
Dialect Differences in Date Functions (concepts: sqlRecursiveCte)
Past correctness, the interviewer escalates to cost: 'this query is slow; what would you change?' Date arithmetic queries have specific performance levers, and the deeper answer names each one rather than gesturing at 'add an index.' Indexing the date column, partition pruning, function-call rewrites to expose predicates to the optimizer, and the EXPLAIN-then-tune discipline. Indexing date columns Partition pruning Most analytical warehouses partition fact tables by date. Snowflake clusters; Big