SQL Date & Time Functions Across Warehouses cheat sheet

Truncation, intervals, time zones, and date spines in Snowflake, BigQuery, Postgres, and DuckDB — side by side.

SQL & DatabasesIntermediate5 sections

Truncation and parts

date_trunc('month', ordered_at): Works in Snowflake, Postgres, and DuckDB. BigQuery reverses the arguments as DATE_TRUNC(ordered_at, MONTH).
date_part('dow', ordered_at): Day of week as an integer. Postgres and DuckDB start Sunday at 0; Snowflake follows the WEEK_START parameter.
extract(year from ordered_at): ANSI standard and portable across all four engines. Prefer it when the query must move between warehouses.
last_day(ordered_at): Month-end date. Safer than adding a month and subtracting a day, which breaks on 31-day boundaries.

dateadd(day, -7, current_date): Snowflake syntax. Postgres and DuckDB use current_date - interval '7 days'; BigQuery uses DATE_SUB.
datediff(day, first_seen, last_seen): Snowflake and BigQuery return whole units. In Postgres, subtracting dates yields an integer of days directly.
ordered_at + interval '1 month': Calendar-aware in Postgres and DuckDB — Jan 31 plus one month lands on Feb 28 or 29, not an error.
timestampdiff(second, started_at, ended_at): Use seconds for durations, then divide. Computing in minutes first silently truncates the remainder.

convert_timezone('UTC', 'America/New_York', event_ts): Snowflake. Always store UTC and convert at the presentation layer; storing local time is unrecoverable.
event_ts at time zone 'UTC' at time zone 'Asia/Kolkata': Postgres double-cast idiom. The first clause interprets, the second renders.
timestamp_ntz vs timestamp_tz vs timestamp_ltz: Snowflake's three types. NTZ ignores zones, TZ stores an offset, LTZ renders in the session zone — pick one per column and document it.
DATETIME vs TIMESTAMP in BigQuery: DATETIME is zone-free wall time; TIMESTAMP is an absolute instant in UTC. Mixing them in joins produces silent misalignment.

alter session set week_start = 1: Snowflake — makes Monday the week start so WEEKISO and DATE_TRUNC('week') agree with the business calendar.
date_trunc('week', ordered_at): Postgres and DuckDB always use ISO weeks starting Monday. BigQuery defaults to Sunday unless you write WEEK(MONDAY).
yearweek / weekiso: ISO week numbering puts early-January dates in week 52 of the prior year. Report against a date dimension instead.

select dateadd(day, seq4(), '2026-01-01'::date) as d from table(generator(rowcount => 365)): Snowflake date spine. Left-join your facts to it so days with no activity still appear as zero.
select unnest(generate_series(date '2026-01-01', date '2026-12-31', interval '1 day')) as d: Postgres and DuckDB spine generator. The cleanest way to fill gaps before a running total.
GENERATE_DATE_ARRAY('2026-01-01', '2026-12-31'): BigQuery equivalent, usually paired with UNNEST in the FROM clause.
coalesce(sum(amount), 0): After a left join to the spine, aggregate with coalesce or the missing days become nulls that break window math.

From DataLane — tutorials at/blog, practice SQL live in theplayground.