Dialect Differences in Date Math
Concepts covered: Date Math, Date Differences, Timezones, Date Parts
Dialect comes up in 2 ways: "we're on Snowflake, write it there", or "this report was ported from SQL Server and the numbers moved". Spelling differences cost a minute of syntax. The expensive ones are calls that read the same and compute something else, and those are what the second phrasing is fishing for.
The same operation in 4 dialects
| Operation | Postgres | BigQuery | Snowflake | Spark SQL |
|---|---|---|---|---|
| Add 1 month | `d + INTERVAL '1 month'` | `DATE_ADD(d, INTERVAL 1 MONTH)` | `DATEADD(month, 1, d)` | `add_months(d, 1)` |
| Days between | `end_date - start_date` | `DATE_DIFF(end_date, start_date, DAY)` | `DATEDIFF(day, start_date, end_date)` | `datediff(end_date, start_date)` |
| UTC instant to Los Angeles wall clock | `ts AT TIME ZONE 'America/Los_Angeles'` | `DATETIME(ts, 'America/Los_Angeles')` | `CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', ts)` | `from_utc_timestamp(ts, 'America/Los_Angeles')` |
| Last day of month | `CAST(DATE_TRUNC('month', d) + INTERVAL '1 month' - INTERVAL '1 day' AS DATE)` | `LAST_DAY(d)` | `LAST_DAY(d)` | `last_day(d)` |
| ISO week and ISO year | `EXTRACT(WEEK FROM d)`, `EXTRACT(ISOYEAR FROM d)` | `EXTRACT(ISOWEEK FROM d)`, `EXTRACT(ISOYEAR FROM d)` | `WEEKISO(d)`, `YEAROFWEEKISO(d)` | `weekofyear(d)`, `extract(YEAROFWEEK FROM d)` |
The day-difference row and the conversion row deserve a second look. The day difference takes its arguments in different orders: BigQuery and Spark put the end first, Snowflake puts the unit first and the start before the end, and Postgres subtracts. A port that keeps the argument positions flips the sign of every duration, and "days to resolve" turns negative. The conversion row changes return types: all 4 engines hand back a value meant to be read as local wall-clock time, so comparing it later against an instant shifts every row by the offset without an error.
Where the Meaning Changes
| Expression | Engine | Result |
|---|---|---|
| `DATEDIFF(year, '2023-12-31', '2024-01-01')` | Snowflake, SQL Server | 1 |
| `TIMESTAMPDIFF(YEAR, '2023-12-31', '2024-01-01')` | MySQL | 0 |
| `DATEADD(month, 1, '2024-02-29')` | Snowflake | 2024-03-29 |
| `ADD_MONTHS('2024-02-29', 1)` | Snowflake | 2024-03-31 |
| `add_months(DATE '2019-02-28', 1)` | Spark 3.x | 2019-03-28 (Spark 2.4 returned 2019-03-31) |
| `DATE_TRUNC(DATE '2024-03-13', WEEK)` | BigQuery | 2024-03-10, a Sunday |
| `DATE_TRUNC('week', DATE '2024-03-13')` | Postgres | 2024-03-11, a Monday |
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.
Handling dialect in the room
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.
- Do
- Ask whether the fiscal year is month-aligned or 52/53-week, and how its years are named
- Convert instants to the region's IANA zone once, and group on the local day only after that
- Measure durations on instants and bucket hours on UTC across DST days
- Compute every recurring date from its anchor with an explicit month-end rule
- Name the dialect, and check DATEDIFF semantics and week start before porting
- Avoid
- Pair EXTRACT(YEAR ...) with an ISO week number
- Truncate in UTC and relabel the bucket as a local day
- Store a fixed offset like '-08:00' for a zone that observes DST
- Chain +1 month steps to build a billing schedule
- Assume DATEDIFF(year, ...) counts completed years
About this lesson
This section is part of the Date Arithmetic: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.