Date Arithmetic: Intermediate

This part of the question rarely announces itself. It arrives as "Q3 bookings", "last week's signups" or "revenue this year", and each phrase names a period whose edges the company chose. Microsoft's year begins in July, the US federal government's in October, and Apple's ends on the last Saturday of September. Before any DATE_TRUNC, the interviewer is listening for the question that fixes which calendar the business counts by.

The quarter_start column answers the follow-up that usually comes next, "now give me fiscal quarter to date". Truncating the shifted date and shifting it back yields the real first day of the fiscal quarter, 2025-01-01 for anything in fiscal Q3, and that value works in a range filter like any other date. The animation below runs the period mapping on the 4 dates that decide most fiscal reports.

Order 1 on 2024-06-30 shifts to 2024-12-30 and closes FY2024 Q4; order 2, a day later, shifts to 2025-01-01 and opens FY2025 Q1. Order 3 on 2025-01-02 sits in calendar Q1 and fiscal Q3, which is the row a DATE_TRUNC('quarter', ...) answer files under the wrong quarter. Order 4 on 2025-06-30 is the last day of FY2025.

A 52/53-week year has boundaries that move. Apple's year ends on the last Saturday of September, so most years run 52 weeks and every fifth or sixth year runs 53, as fiscal 2023 did. No month shift reproduces that, and a 4-5-4 retail calendar adds week-based months on top. The answer is a calendar table keyed by date that carries fiscal_year plus fiscal_quarter and fiscal_week. Build it from the dates finance publishes and join it on the order's date. Say the split out loud: arithmetic for a month-aligned year, a lookup for anything week-based, because there the boundary is a published date rather than a rule.

The usual bug comes from pairing the calendar year with the ISO week. EXTRACT(YEAR ...) next to an ISO week number turns December 30 and 31, 2024 into the key (2024, 1), the same key as January 1 to 7, 2024, so a weekly report adds 2 days of late-December revenue into a week from 12 months earlier. Pair the ISO week with the ISO year, or group by the Monday that starts each week and skip both numbers: a week-start date is unique per week, so it never collides as a join key or a chart label. Which weekday a given engine treats as the start is the part to confirm against the engine in front of you.

In Postgres and Redshift, AT TIME ZONE runs in opposite directions depending on its input. Applied to a TIMESTAMP WITH TIME ZONE, an instant, it returns a TIMESTAMP with no zone that holds the wall clock in the named zone. Applied to a plain TIMESTAMP, it reads the value as wall-clock time in the named zone and returns the instant. The keyword stays the same and the input type picks the direction.

The bug shows up in the second case written with a single AT TIME ZONE. The plain value 2024-03-01 03:30 is read as 03:30 in Los Angeles, which is the instant 11:30 UTC, so the conversion moves order A 8 hours later instead of 8 hours earlier. The result sits 16 hours from the right answer, and an error of exactly twice a zone's offset is the signature of a conversion run backwards. Ask the question that prevents it before writing anything: is order_ts an instant or a wall-clock reading, and in which zone?

The padded range on the stored column keeps the scan bounded, since no zone sits more than 14 hours from UTC, and the exact filter runs on the converted date. Writing the conversion once, in the CTE, means the GROUP BY and the filter agree about which day a row is on, and so does any window function downstream.

One more question decides the join: whose clock? A store's sales belong to the store's zone and a customer's activity streak to the customer's zone. A company-wide daily total needs one zone for everyone, usually headquarters or UTC. Each is a different source for tz_name, and stating which you picked is half the answer.

Interval math splits into 2 kinds that agree on every other day of the year. In Postgres, adding INTERVAL '1 day' to a TIMESTAMP WITH TIME ZONE keeps the wall-clock time in the session's zone, while INTERVAL '24 hours' adds 24 elapsed hours. Across the spring change they land an hour apart.

Durations go wrong the same way when computed from naive local values. A support ticket opened at 00:30 and closed at 05:30 local time on 2024-03-10 shows 5 hours, but 4 hours passed; the same ticket on 2024-11-03 shows 5 hours when 6 passed. Any SLA threshold near those values gets the wrong verdict twice a year. Subtract instants instead, whether TIMESTAMP WITH TIME ZONE values or UTC, and the elapsed time comes out right every day.

Joining hours_in_day onto a daily revenue table turns the November question into a sentence: divide by it, compare revenue per hour, and the jump disappears. The interviewer is listening for that move, because it keeps the local day finance asked for and removes the clock from the comparison.

This one arrives as "when is the next billing date?", "compare to the same day last year" or "retention 1 month after signup". A month has no fixed length, so + 1 month is a rule the engine applies to the calendar fields rather than a quantity of time. Nearly every engine applies the same rule for a single step: add to the month and keep the day, clipping it to the month's last day when it overflows.

The clip also makes month math irreversible. Taking 2024-01-31 forward 1 month and back 1 month returns 2024-01-29, so a filter written as renewal_date - INTERVAL '1 month' = start_date misses every start that was clipped. And "1 month" and "30 days" part ways at once: 2024-01-31 plus 30 days is 2024-03-01, plus 1 month is 2024-02-29, and a trial sold as a month but coded as 30 days ends on a different date for most starts in a month that is not 30 days long. The exceptions are starts where the clip happens to land on the same day, such as 2024-01-30, which reaches 2024-02-29 either way.

Joined on ly.day = ty.day - INTERVAL '1 year', the 2023-02-28 row matches 2 rows this year, so last year's February counts that day twice and the growth figure comes out low. Written the other way, ly.day + INTERVAL '1 year' = ty.day, February 29 matches nothing. The business has to say whether February 29 compares to February 28 or to nothing. Retail usually sidesteps it by comparing to 364 days back, which keeps a Saturday against a Saturday at the price of the calendar dates drifting apart year after year.

DATEDIFF in Snowflake and SQL Server counts unit boundaries crossed, so a single day across New Year is 1 year, and BigQuery's DATE_DIFF counts boundaries the same way at the month and year level. MySQL's TIMESTAMPDIFF and Postgres's AGE count completed units. A tenure report moved from MySQL to Snowflake adds a year to every employee whose anniversary is still ahead this year, and nothing errors.

Snowflake ships 2 month functions with different month-end rules: ADD_MONTHS keeps a last-of-month date on the last day, DATEADD keeps the day number. Spark changed add_months in 3.0 to stop preserving month ends, so the same query returns different dates on 2.4 and 3.x clusters. Week truncation splits on the first weekday: Postgres starts weeks on Monday, BigQuery's WEEK starts on Sunday unless written WEEK(MONDAY) or ISOWEEK, and Snowflake follows the session's WEEK_START parameter.

The type name itself moves. A BigQuery TIMESTAMP is an instant; a Postgres TIMESTAMP is a wall-clock value, as is Snowflake's default TIMESTAMP_NTZ; Spark's default TIMESTAMP is an instant shown in the session zone. Functions that read the clock depend on the session too. Snowflake's TIMEZONE parameter defaults to America/Los_Angeles, so CURRENT_DATE there rolls over at Los Angeles midnight unless someone changed it, while BigQuery's CURRENT_DATE() uses UTC unless given a zone.

Return types shift under smaller calls as well. In Postgres, a DATE plus an integer is a DATE, while a DATE plus an INTERVAL is a TIMESTAMP, which is why the month-add queries in this lesson cast back to DATE before grouping or joining on the result.

Name a dialect in the first minute and write in it. Then name the places where your answer depends on it, usually 2 of the rows above: "I'm writing Postgres; in Snowflake I'd use ADD_MONTHS here because the anchor is a month end, and I'd check WEEK_START before trusting the week buckets." Interviewers rarely grade exact spelling. They are listening for whether you know which calls change meaning when the engine changes, because those are the calls that break silently in a migration.