BeginnerSQL · 25 min

Date Arithmetic: Beginner

Date arithmetic is the most-used SQL feature in production data engineering. Every reporting query touches dates: rolling 30-day active users, monthly revenue, weekly cohort retention, year-to-date totals, business-hours filtering. The candidate who can confidently write DATE_TRUNC, INTERVAL arithmetic, EXTRACT, and DATE_DIFF without reaching for documentation is the candidate who has built dashboards before. The candidate who fumbles the syntax in an interview signals inexperience even on questions where the SQL logic is straightforward. This lesson teaches you the toolkit, the canonical patterns, and the engine-specific quirks that catch people out before they've shipped date-heavy reporting.

Use DATE_TRUNC to collapse dates into reporting buckets (month, week, quarter)

Compute date offsets with INTERVAL arithmetic (90 days ago, 1 year from now)

Extract date parts with EXTRACT for filtering and grouping by month or year

Compute date differences with DATE_DIFF or subtraction; understand the unit conventions

Dates Hide in Every Business Question

Daily Life

Interviews

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

Here is the question every reporting interview asks in some form: 'show me revenue rolled up by month for the past 12 months, with each month's number compared to the same month a year ago.' The candidate who writes DATE_TRUNC('month', txn_date) for grouping, txn_date >= CURRENT_DATE - INTERVAL '12 months' for filtering, and txn_date - INTERVAL '1 year' for the year-ago comparison produces a clean query in eight lines. The candidate who tries to do the same thing with string manipulation on the date column, or who uses BETWEEN with hardcoded dates, produces a query that works for today's interview but breaks in production the moment the month rolls over.

You are being tested on date arithmetic when you hear:

▸"revenue rolled up by month / week / quarter"
▸"users active in the last N days"
▸"this month vs the same month last year"
▸"cohort defined by signup week"
▸"orders placed during business hours"
▸Any question involving rolling windows, period truncation, or relative dates

The four-tool date toolkit

Four functions cover 90% of date arithmetic in production SQL. DATE_TRUNC truncates a date or timestamp to a coarser unit; DATE_TRUNC('month', '2024-03-15') returns '2024-03-01.' This is the workhorse for grouping. INTERVAL arithmetic adds or subtracts time spans; CURRENT_DATE - INTERVAL '30 days' returns the date 30 days ago. This is the workhorse for windowing. EXTRACT pulls a part out of a date or timestamp; EXTRACT(YEAR FROM txn_date) returns 2024. This is the workhorse for filtering by year/month/dow. DATE_DIFF computes the difference between two dates in a specified unit; DATE_DIFF('day', signup_date, CURRENT_DATE) returns the days since signup. This is the workhorse for age and tenure.

The four-tool date toolkit:

▸DATE_TRUNC: truncate to a coarser unit (month/week/quarter) for grouping
▸INTERVAL: add or subtract time spans (CURRENT_DATE - INTERVAL '30 days')
▸EXTRACT: pull a part (YEAR, MONTH, DOW) for filtering or grouping
▸DATE_DIFF: difference between two dates in a named unit

•Weak date handling

Uses string operations on date columns (LEFT(date_col, 7) for month)
Hardcodes date ranges that drift as the calendar moves
Mixes DATE and TIMESTAMP columns without thinking about timezone
Reaches for engine-specific functions without checking portability

✓Strong date handling

Uses DATE_TRUNC for any month/week/quarter grouping
Uses INTERVAL arithmetic for relative date windows
Names the engine being targeted (Postgres, Snowflake, BigQuery)
Considers timezone when the column is a timestamp

Why companies care

Date arithmetic is the most error-prone area of production SQL. Off-by-one mistakes in date ranges (BETWEEN includes both ends; > and < exclude). Daylight saving time bugs (2 AM happens twice in November in US Eastern; midnight on a DST boundary is ambiguous). Timezone confusion (UTC midnight is not the same as Pacific midnight). End-of-month edge cases (February 30 does not exist; INTERVAL '1 month' added to January 31 gives different results on different engines). Every senior data engineer has shipped a date bug; the pattern recurs because the failure modes are subtle. The interview tests this not because date arithmetic is conceptually hard, but because the candidate's fluency with the toolkit predicts how often they will ship a date bug in production.

DATE_TRUNC for Day, Week, and Month Buckets

Daily Life

Interviews

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

DATE_TRUNC is the function you reach for whenever the question asks for 'by month' or 'by week' or 'by quarter.' It takes a unit and a date or timestamp; it returns the date or timestamp truncated to the start of that unit. DATE_TRUNC('month', '2024-03-15') returns '2024-03-01.' DATE_TRUNC('week', '2024-03-15') returns the Monday or Sunday of that week (depending on the engine). DATE_TRUNC('quarter', '2024-03-15') returns '2024-01-01.' The truncated value is what you GROUP BY when the consumer wants monthly or weekly or quarterly aggregates.

collapses dates within a unit into the same bucket; one row per (region, month_start).
anchors the start of a rolling window: DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'.
names the bucket so the dashboard renders month_start as a date column.

The canonical pattern

	/* Revenue per month per region for the last 12 months */
	SELECT
	region,
	DATE_TRUNC('month', txn_date) AS month_start,
	SUM(amount) AS monthly_revenue
	FROM transactions
	WHERE txn_date >= DATE_TRUNC(
	'month',
	CURRENT_DATE
	) - INTERVAL '12 months'
	GROUP BY region, DATE_TRUNC('month', txn_date)
	ORDER BY region, month_start

Reading the pattern

DATE_TRUNC('month', txn_date) in the SELECT and GROUP BY collapses every transaction date in March 2024 (March 1 through March 31) into the single value '2024-03-01.' All transactions in that month group together; SUM(amount) computes the total for that (region, month) pair. The output has one row per (region, month). The WHERE clause uses DATE_TRUNC again to anchor the start of the rolling 12-month window: DATE_TRUNC('month', CURRENT_DATE) is the first of the current month; subtracting INTERVAL '12 months' gives the first of the month a year ago. The result is exactly 12 months of data ending with the current (partial) month.

The available units

Most engines support: 'year,' 'quarter,' 'month,' 'week,' 'day,' 'hour,' 'minute,' 'second.' Some engines extend with 'decade,' 'century,' 'millennium' (rare). The unit string is what makes DATE_TRUNC versatile; pick the unit that matches the reporting grain. For weekly cohort reports, 'week.' For monthly revenue, 'month.' For hourly system metrics, 'hour.'

The week-boundary trap

DATE_TRUNC('week', date) returns the start of the week, but engines disagree on which day starts the week. Postgres uses ISO 8601 (Monday). MySQL uses Sunday by default but is configurable. BigQuery uses Sunday by default but DATE_TRUNC accepts an optional weekday argument. Snowflake uses Monday by default but the WEEK_START parameter changes it session-wide. The implication: weekly reports may show different week boundaries on different engines, and a query that worked on Postgres may report different numbers on BigQuery. State the convention when you write the query: 'this is using Postgres's Monday-start week convention; on BigQuery I would explicitly write DATE_TRUNC(date, WEEK(MONDAY)).'

Engine-specific syntax

The function call varies by engine. Postgres: DATE_TRUNC('month', txn_date). Snowflake: DATE_TRUNC('month', txn_date) (same syntax). BigQuery: DATE_TRUNC(txn_date, MONTH) (note: column first, unit second, unquoted unit). SQL Server: DATEFROMPARTS(YEAR(d), MONTH(d), 1) (no native DATE_TRUNC; the equivalent is reconstruction). The function call shape is engine-specific; the concept is universal. State the engine when writing the query so the interviewer knows you are aware of the portability question.


	DATE_TRUNC('month', txn_date) DATE_TRUNC(txn_date, MONTH) DATEFROMPARTS(YEAR(txn_date), MONTH(txn_date), 1) DATEADD(month, DATEDIFF(month, 0, txn_date), 0) DATE_FORMAT(txn_date, '%Y-%m-01')

TIP

Memorize the Postgres syntax for DATE_TRUNC because most SQL tutorials and online resources use it. When you write the function in an interview, name the engine you are targeting if it is anything other than Postgres or Snowflake. The interviewer reads engine awareness as production-experience even on basic questions.

DATE_ADD and DATE_DIFF Basics

Daily Life

Interviews

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

INTERVAL is the SQL primitive for adding or subtracting time spans. CURRENT_DATE + INTERVAL '7 days' returns next week. signup_date + INTERVAL '90 days' returns the date 90 days after signup. The result is a date or timestamp (depending on the input); the arithmetic respects calendar rules (month and year intervals handle variable-length months correctly). INTERVAL is the workhorse for relative date windows.

The canonical windowing patterns

	WHERE txn_date >= CURRENT_DATE - INTERVAL '30 days'


	WHERE txn_date >= DATE_TRUNC('month', CURRENT_DATE)


	WHERE txn_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AND txn_date < DATE_TRUNC('month', CURRENT_DATE)


	WHERE txn_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months' AND txn_date < DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '11 months'


	WHERE txn_date >= DATE_TRUNC('week', CURRENT_DATE - INTERVAL '52 weeks')


	SELECT
	customer_id,
	signup_date,
	signup_date + INTERVAL '90 days' AS retention_window_end
	FROM customers ;

INTERVAL units and how engines parse them

Most engines accept the same set of unit strings: 'seconds,' 'minutes,' 'hours,' 'days,' 'weeks,' 'months,' 'years.' The number can be any integer, positive or negative. INTERVAL '1 day,' INTERVAL '30 days,' INTERVAL '-7 days' are all valid. Engines vary in how they parse the literal: Postgres accepts 'X days' as a quoted string after INTERVAL, BigQuery uses INTERVAL N DAY (unquoted, singular), Snowflake supports both forms. State the syntax when writing: 'using Postgres-style INTERVAL strings; on BigQuery I would write INTERVAL 30 DAY (singular, unquoted).'

BETWEEN vs >= AND <

Two ways to write a date range filter; both work for some cases. BETWEEN start AND end is inclusive on both sides; the end date is included in the range. >= start AND < end is left-inclusive and right-exclusive (the half-open interval). For date ranges, half-open is almost always what you want because it avoids the ambiguity of 'does the end include the entire end day or just midnight?' WHERE txn_date >= '2024-01-01' AND txn_date < '2024-02-01' captures all of January regardless of whether txn_date is a date or a timestamp. WHERE txn_date BETWEEN '2024-01-01' AND '2024-01-31' captures all of January if txn_date is a date but excludes most of January 31 if txn_date is a timestamp (because BETWEEN treats '2024-01-31' as midnight-only). Always prefer the half-open form for timestamp columns.

Half-open vs BETWEEN: why production prefers half-open

▸BETWEEN A AND B is inclusive on both sides
▸BETWEEN on TIMESTAMP misses most of the last day (midnight-only)
▸>= A AND < B (half-open) captures the full period regardless of granularity
▸Production rule: always half-open for timestamp columns

•BETWEEN: inclusive on both sides

Works for date columns (whole-day granularity)
Misses most of the last day for timestamp columns
Reads as English but hides the boundary subtlety
Off-by-one risk when the column type changes

✓Half-open >= AND <: production safe

Works for both date and timestamp columns
Captures the entire end period regardless of granularity
Reads as 'starts at A, runs until B' without ambiguity
The convention in production data engineering

The 'cohort retention window' pattern

A common DE use case: for each customer who signed up in a given week, count how many are still active 90 days later. The query needs each customer's individual 90-day window (signup_date + INTERVAL '90 days'). The pattern:

	/* Customers who were still active 90 days after signup */
	SELECT
	c.customer_id,
	c.signup_date,
	c.signup_date + INTERVAL '90 days' AS retention_check_date,
	CASE
	WHEN EXISTS (
	SELECT
	1
	FROM events AS e
	WHERE e.customer_id = c.customer_id
	AND e.event_date >= c.signup_date + INTERVAL '89 days'
	AND e.event_date < c.signup_date + INTERVAL '91 days'
	) THEN 'retained'
	ELSE 'churned'
	END AS day_90_status
	FROM customers AS c
	WHERE c.signup_date >= '2024-01-01'
	AND c.signup_date < '2024-02-01'

Each customer has their own retention window because signup_date varies per customer. The INTERVAL arithmetic on a per-row basis is the pattern. The +/- 1 day around the 90-day mark gives a 2-day tolerance window for the activity check; some implementations use exactly +90 days, others use a tolerance band. State the tolerance choice when writing: 'I'm using a 2-day window around day 90 to capture activity that happened slightly before or after; an exact-day check would miss customers who were active on day 89 or 91.'

Month and year interval gotchas

INTERVAL '1 month' added to January 31 gives a different result on different engines because February does not have 31 days. Postgres returns February 28 (or 29 in leap years). MySQL returns February 28 (truncates). BigQuery returns February 28. Snowflake returns February 28. The behavior is generally consistent (truncate to the last day of the target month), but the failure mode is subtle: adding INTERVAL '1 month' three months in a row from January 31 gives April 28 (Jan 31 -> Feb 28 -> Mar 28 -> Apr 28), not April 30. If you need exact 30-day windows, use INTERVAL '30 days,' not INTERVAL '1 month.' State the choice based on the consumer's intent: 'I'm using days for fixed-length windows and months for calendar-aware windows; the two are not interchangeable.'

INTERVAL also accepts compound forms in some engines: INTERVAL '1 year 6 months' on Postgres. This is convenient but engine-specific. For portability, prefer separate INTERVAL clauses chained together: INTERVAL '1 year' + INTERVAL '6 months.' The arithmetic is the same; the syntax is more universal.

Filtering a Date Range Correctly

Daily Life

Interviews

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

EXTRACT pulls a part out of a date or timestamp value. EXTRACT(YEAR FROM txn_date) returns 2024 from the date '2024-03-15.' EXTRACT(MONTH FROM ...) returns 3. EXTRACT(DOW FROM ...) returns the day of week (0-6, with 0 being Sunday on most engines). EXTRACT is the tool for filtering by date component (only December rows; only Mondays) or for grouping by date component (revenue per month-of-year across all years).

The EXTRACT pattern

	SELECT
	EXTRACT(MONTH FROM txn_date) AS month_of_year,
	SUM(amount) AS total_sales
	FROM transactions
	GROUP BY EXTRACT(MONTH FROM txn_date)
	ORDER BY month_of_year ;


	SELECT *
	FROM transactions
	WHERE EXTRACT(DOW FROM txn_date) = 1 ;


	SELECT
	EXTRACT(YEAR FROM txn_date) AS year,
	EXTRACT(QUARTER FROM txn_date) AS quarter,
	SUM(amount) AS quarterly_revenue
	FROM transactions
	GROUP BY EXTRACT(YEAR FROM txn_date), EXTRACT(QUARTER FROM txn_date)
	ORDER BY year, quarter ;

Available EXTRACT fields

Standard fields: YEAR, QUARTER, MONTH, WEEK, DAY, DAYOFYEAR, DAYOFWEEK (or DOW), HOUR, MINUTE, SECOND, EPOCH (Unix seconds since 1970). Each engine has its own conventions for DAYOFWEEK numbering. Postgres ISO: 0 = Sunday, 6 = Saturday. MySQL: 1 = Sunday, 7 = Saturday. BigQuery: 1 = Sunday, 7 = Saturday. Snowflake: depends on session settings. When filtering by day of week, state the convention you are using: 'Monday is 1 on Postgres ISO; if this ships to BigQuery, the value changes.'

DATE_DIFF for differences

DATE_DIFF returns the difference between two dates in a specified unit. DATE_DIFF('day', start_date, end_date) returns the number of days. The unit is the first argument; the dates follow. Most engines support 'second,' 'minute,' 'hour,' 'day,' 'week,' 'month,' 'quarter,' 'year' as units. The function is the right tool for age and tenure calculations: 'days since signup,' 'months as a customer,' 'years since founded.'

DATE_DIFF('day', '2024-01-01 23:00', '2024-01-02 01:00') = 1 even though only 2 hours.
BigQuery: column-first, unquoted unit; Snowflake: unit-first, quoted; Postgres: subtraction.
use DATE_DIFF for calendar-day counts; use timestamp subtraction (seconds) for elapsed-time calculations.

	SELECT
	customer_id,
	signup_date,
	DATE_DIFF('day', signup_date, CURRENT_DATE) AS days_active
	FROM customers ;


	SELECT *
	FROM customers
	WHERE DATE_DIFF('year', signup_date, CURRENT_DATE) >= 1 ;

Engine-specific DATE_DIFF syntax

This is one of the most varied syntaxes across engines. BigQuery: DATE_DIFF(end_date, start_date, DAY) (end first, unit last, unit unquoted). Snowflake: DATEDIFF('day', start_date, end_date) (unit quoted, start first). SQL Server: DATEDIFF(day, start_date, end_date) (no quotes, unit unquoted). Postgres: end_date - start_date returns an interval; EXTRACT(DAY FROM end_date - start_date) is the portable form. Postgres also has age(end, start) for human-readable differences. Always check the engine convention before writing the query.


	DATE_DIFF(end_date, start_date, DAY) DATEDIFF('day', start_date, end_date) DATEDIFF(day, start_date, end_date)(end_date - start_date) EXTRACT(DAY FROM end_date - start_date)

The 'inclusive vs exclusive end' question

DATE_DIFF returns the number of unit-boundaries crossed between two dates, not the number of unit-intervals. DATE_DIFF('day', '2024-01-01', '2024-01-02') is 1 (one day boundary crossed). DATE_DIFF('day', '2024-01-01 23:00', '2024-01-02 01:00') is 1 (one day boundary crossed, even though only 2 hours elapsed). For 'how many full days,' this works. For 'how many 24-hour periods,' use TIMESTAMP arithmetic with EPOCH or seconds. State the choice when writing: 'I'm using DATE_DIFF for calendar-day counts; for elapsed-time calculations I'd subtract timestamps and convert to seconds.'

•DATE_DIFF semantics

Counts unit-boundaries crossed, not unit-intervals elapsed
DAY counts midnight crossings (1 second after midnight is 1 day later)
MONTH counts month-boundary crossings, not 30-day intervals
Good for 'days since signup' calendar counts; bad for 'hours of uptime'

✓Timestamp subtraction semantics

Returns an interval representing elapsed time (Postgres) or numeric seconds (others)
Captures sub-day precision down to microseconds
Good for elapsed-time, SLA, and duration calculations
Convert to days with seconds / 86400 or extract DAY from interval

Filtering by date component vs date range

Two patterns that look similar but produce different results. EXTRACT(MONTH FROM txn_date) = 3 returns all March rows across all years. txn_date >= '2024-03-01' AND txn_date < '2024-04-01' returns March 2024 rows only. The first pattern is seasonality analysis (March across years); the second is point-in-time reporting (this March specifically). Pick based on the consumer's question: 'all March' vs 'March 2024.' State the choice when writing: 'EXTRACT for seasonality across years; date range for a specific year's data.'

TIP

When you compute a date difference, name the unit explicitly even if the result is 'just a number.' DATE_DIFF('day', signup_date, CURRENT_DATE) AS days_active is better than DATE_DIFF(signup_date, CURRENT_DATE) AS dt. The named alias tells the next reader (and you, six months later) what unit the number is in. Date difference columns without units are a common source of bugs when someone misreads days as hours.

Half-Open Intervals vs BETWEEN

Daily Life

Interviews

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

The two areas where date arithmetic ships the most bugs in production: timezone handling and engine portability. Both have specific patterns and known mitigations. Naming them in the interview, even briefly, reads as production-experience that the basic-syntax candidate does not have.

The timezone gotcha

Dates and timestamps without timezone (TIMESTAMP, DATE) are ambiguous; their meaning depends on where the database thinks the user is. Timestamps with timezone (TIMESTAMPTZ on Postgres, TIMESTAMP_TZ on Snowflake) are unambiguous; they store an absolute moment in time. When the column is TIMESTAMPTZ, the engine converts to the session timezone for display and arithmetic; when the column is TIMESTAMP, the value is interpreted as-is. Mixing the two columns or comparing across timezones is the single largest source of date bugs in production.

Daylight saving time creates a specific failure mode. In US Eastern, the local clock skips 2 AM in March and repeats 1 AM in November. A timestamp '2024-03-10 02:30 US/Eastern' does not exist; engines either throw or default to the next valid time. A timestamp '2024-11-03 01:30 US/Eastern' is ambiguous; it occurs twice. UTC has no DST; comparisons and arithmetic in UTC are unambiguous. The production rule: store timestamps as UTC; do timezone conversion at the display layer only.

Engine portability summary

Five things vary across engines: function naming (DATE_TRUNC vs DATEFROMPARTS), unit syntax (quoted strings vs unquoted keywords), week-start day (Monday vs Sunday), day-of-week numbering (0-6 vs 1-7), and DATE_DIFF argument order. The mitigations: name the engine you are targeting when writing the query, document the convention in comments for any column that uses an engine-default behavior, and prefer the portable form when shipping cross-engine code. The portable form is usually verbose but unambiguous.

Operation	Postgres	BigQuery	Snowflake
Truncate to month	DATE_TRUNC('month', d)	DATE_TRUNC(d, MONTH)	DATE_TRUNC('month', d)
Add 30 days	d + INTERVAL '30 days'	DATE_ADD(d, INTERVAL 30 DAY)	DATEADD('day', 30, d)
Extract year	EXTRACT(YEAR FROM d)	EXTRACT(YEAR FROM d)	YEAR(d) or EXTRACT(...)
Diff in days	(end - start)::INT	DATE_DIFF(end, start, DAY)	DATEDIFF('day', start, end)
Current date	CURRENT_DATE	CURRENT_DATE()	CURRENT_DATE()

Common bugs and their mitigations

Five date-arithmetic bugs that ship to production. First: hardcoded dates that drift. WHERE txn_date >= '2024-01-01' captures every year of data after 2024 starts; the query was written for early 2024 and never gets revisited. Mitigation: use CURRENT_DATE - INTERVAL '... days' for rolling windows. Second: BETWEEN on timestamps. WHERE ts BETWEEN '2024-01-01' AND '2024-01-31' misses most of January 31. Mitigation: half-open intervals (>= AND <). Third: timezone-naive comparisons. WHERE ts >= '2024-01-01' on a TIMESTAMPTZ column applies the session timezone; the result varies per user. Mitigation: explicit UTC ('2024-01-01 00:00:00 UTC'::TIMESTAMPTZ). Fourth: month arithmetic off-by-one. INTERVAL '1 month' from January 31 lands on February 28, not March 3. Mitigation: use DATE_TRUNC + INTERVAL together for canonical month boundaries. Fifth: week-start drift across engines. DATE_TRUNC('week', ...) returns Monday on Postgres and Sunday on others. Mitigation: state the engine and the convention.

Hardcoded dates that drift as calendar movesBETWEEN on timestamps (loses end-of-day)Session-timezone DATE_TRUNC on TIMESTAMPTZINTERVAL '1 month' off-by-one on month-end datesWeek-start convention drift across engines

At Coinbase in 2021, the daily revenue dashboard had a timezone-induced bug that under-reported revenue by ~4% for a month before anyone caught it. The transactions table had ts_utc as a TIMESTAMPTZ column. The dashboard query used DATE_TRUNC('day', ts_utc) with no explicit timezone conversion, which truncated in the session timezone (Pacific). For transactions between 4 PM and midnight Pacific (which is 0:00 to 8:00 UTC the next day), the truncation produced the previous calendar day rather than the day the transaction actually occurred in UTC. The dashboard's daily totals shifted by 8 hours, and roughly 4% of each day's revenue was attributed to the previous day. The fix was DATE_TRUNC('day', ts_utc AT TIME ZONE 'UTC') to make the conversion explicit. The runbook line was 'every DATE_TRUNC against a TIMESTAMPTZ column in this codebase has an explicit AT TIME ZONE; default-session behavior is a CI lint failure.' Candidates who name the AT TIME ZONE clause unprompted read as someone who has been on the wrong side of a session-timezone bug.

Situation	Phrasing that flatlines	Phrasing that lands
You see 'revenue by month for last year'	"GROUP BY MONTH(date)."	"DATE_TRUNC('month', txn_date) in SELECT and GROUP BY, plus a half-open WHERE filter for the time range."
The interviewer asks 'last 30 days'	"BETWEEN '2024-01-01' AND '2024-01-31'."	"WHERE txn_date >= CURRENT_DATE - INTERVAL '30 days'. Half-open interval avoids the end-of-day ambiguity; rolling window doesn't drift as the date moves."
The data has timestamps in UTC, dashboard in Pacific	"DATE_TRUNC('day', ts)."	"DATE_TRUNC('day', ts AT TIME ZONE 'America/Los_Angeles') if I want Pacific-day buckets. Otherwise UTC truncation, and the consumer's display layer handles timezone."
The interviewer asks 'year over year by month'	"BETWEEN dates."	"DATE_TRUNC('month', txn_date) for the bucket; txn_date - INTERVAL '12 months' for the comparison column. Both pre-aggregated in a CTE before the LAG."
The engine is BigQuery	"DATE_TRUNC('month', date)."	"DATE_TRUNC(date, MONTH) on BigQuery (column first, unquoted unit). DATE_TRUNC('month', date) is Postgres / Snowflake."

The closing summary

Close with a four-sentence wrap. 'Date arithmetic in SQL has four primary tools: DATE_TRUNC for grouping, INTERVAL for windowing, EXTRACT for filtering by component, and DATE_DIFF for differences. The patterns are: DATE_TRUNC + GROUP BY for monthly/weekly aggregates; CURRENT_DATE - INTERVAL for rolling windows; half-open intervals (>= AND <) instead of BETWEEN for timestamp ranges; and explicit AT TIME ZONE for any timezone-aware column. Engine syntax varies (Postgres, Snowflake, BigQuery each have minor differences); state the engine when writing. The biggest production failure modes are timezone-naive comparisons and BETWEEN-on-timestamp; both have known mitigations.' Four sentences. Toolkit, patterns, portability, failure modes. The shape generalizes to every date-arithmetic question.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a data engineering phone screen at a marketplace company. The interviewer asks: 'Show me revenue rolled up by month for the past 12 months, and add a column comparing each month to the same month a year ago.'

You say: 'I'll use DATE_TRUNC for the monthly bucket, INTERVAL arithmetic for the 12-month filter and the year-ago comparison.'

You write three CTEs: monthly aggregates with DATE_TRUNC('month', txn_date), prior_year by self-joining on DATE_TRUNC('month', txn_date) = current.month - INTERVAL '12 months', final SELECT computing the delta.

You name the WHERE filter: 'txn_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months' gives me a rolling 12-month window anchored to the start of the current month.'

Follow-up: 'What if I use BETWEEN?' You say: 'BETWEEN on a timestamp column misses most of the last day; I use half-open intervals (>= AND <) which capture the entire end period regardless of granularity. The convention in production DE is the half-open form.'

Follow-up: 'The transactions table has txn_ts as TIMESTAMPTZ in UTC. The dashboard shows Pacific buckets.' You say: 'DATE_TRUNC('month', txn_ts AT TIME ZONE 'America/Los_Angeles'). The AT TIME ZONE makes the conversion explicit; default session timezone is a CI lint failure in production codebases.'

Closing: 'On Postgres I use DATE_TRUNC and INTERVAL syntax; on BigQuery I would switch to DATE_TRUNC(col, MONTH) and DATE_ADD with INTERVAL 12 DAY; on Snowflake the syntax matches Postgres mostly.' Engine awareness reads as production-experience.

KEY TAKEAWAYS

Four tools cover almost all production date work: DATE_TRUNC for grouping to a reporting grain, INTERVAL for rolling windows, EXTRACT for filtering by component, and DATE_DIFF for differences.

Prefer half-open ranges: txn_date >= '2024-01-01' AND txn_date < '2024-02-01' captures all of January whether the column is a date or a timestamp, while BETWEEN through '2024-01-31' loses everything after midnight on the last day.

Hardcoded start dates drift as time passes. Anchor rolling windows on CURRENT_DATE - INTERVAL '30 days' or DATE_TRUNC('month', CURRENT_DATE) so the query stays correct without revisiting.

Store timestamps in UTC and convert only at the display layer. DST means 2:30 AM Eastern does not exist in March and 1:30 AM occurs twice in November, so local-time arithmetic is ambiguous by design.

Engine defaults differ in five places: function naming, quoted versus unquoted units, week start day, day-of-week numbering, and DATE_DIFF argument order. Name the target engine when you write the query.

INTERVAL '1 month' is calendar arithmetic, so January 31 plus one month three times lands on April 28, not April 30. Use INTERVAL '30 days' when you need a fixed-length window.

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

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

Topics covered: Dates Hide in Every Business Question, DATE_TRUNC for Day, Week, and Month Buckets, DATE_ADD and DATE_DIFF Basics, Filtering a Date Range Correctly, Half-Open Intervals vs BETWEEN

Lesson Sections

Dates Hide in Every Business Question (concepts: sqlDateTrunc)
The four-tool date toolkit Four functions cover 90% of date arithmetic in production SQL. DATE_TRUNC truncates a date or timestamp to a coarser unit; DATE_TRUNC('month', '2024-03-15') returns '2024-03-01.' This is the workhorse for grouping. INTERVAL arithmetic adds or subtracts time spans; CURRENT_DATE - INTERVAL '30 days' returns the date 30 days ago. This is the workhorse for windowing. EXTRACT pulls a part out of a date or timestamp; EXTRACT(YEAR FROM txn_date) returns 2024. This is the work
DATE_TRUNC for Day, Week, and Month Buckets (concepts: sqlDateTrunc)
DATE_TRUNC is the function you reach for whenever the question asks for 'by month' or 'by week' or 'by quarter.' It takes a unit and a date or timestamp; it returns the date or timestamp truncated to the start of that unit. DATE_TRUNC('month', '2024-03-15') returns '2024-03-01.' DATE_TRUNC('week', '2024-03-15') returns the Monday or Sunday of that week (depending on the engine). DATE_TRUNC('quarter', '2024-03-15') returns '2024-01-01.' The truncated value is what you GROUP BY when the consumer w
DATE_ADD and DATE_DIFF Basics (concepts: sqlDateAdd)
INTERVAL is the SQL primitive for adding or subtracting time spans. CURRENT_DATE + INTERVAL '7 days' returns next week. signup_date + INTERVAL '90 days' returns the date 90 days after signup. The result is a date or timestamp (depending on the input); the arithmetic respects calendar rules (month and year intervals handle variable-length months correctly). INTERVAL is the workhorse for relative date windows. The canonical windowing patterns INTERVAL units and how engines parse them Most engines
Filtering a Date Range Correctly (concepts: sqlDateDiff)
EXTRACT pulls a part out of a date or timestamp value. EXTRACT(YEAR FROM txn_date) returns 2024 from the date '2024-03-15.' EXTRACT(MONTH FROM ...) returns 3. EXTRACT(DOW FROM ...) returns the day of week (0-6, with 0 being Sunday on most engines). EXTRACT is the tool for filtering by date component (only December rows; only Mondays) or for grouping by date component (revenue per month-of-year across all years). The EXTRACT pattern Available EXTRACT fields Standard fields: YEAR, QUARTER, MONTH,
Half-Open Intervals vs BETWEEN (concepts: sqlRecursiveCte)
The two areas where date arithmetic ships the most bugs in production: timezone handling and engine portability. Both have specific patterns and known mitigations. Naming them in the interview, even briefly, reads as production-experience that the basic-syntax candidate does not have. The timezone gotcha Daylight saving time creates a specific failure mode. In US Eastern, the local clock skips 2 AM in March and repeats 1 AM in November. A timestamp '2024-03-10 02:30 US/Eastern' does not exist; e