Truncate a Timestamp to Day/Week/Month in SQL (DATE_TRUNC per Dialect)

Truncating to the start of a period is the basis of every “by month” / “by week” report.

Truncate to the first of the month

-- PostgreSQL
SELECT DATE_TRUNC('month', ts);

-- BigQuery   (column first, unit unquoted)
SELECT DATE_TRUNC(ts, MONTH);

-- Snowflake
SELECT DATE_TRUNC('month', ts);

-- DuckDB
SELECT DATE_TRUNC('month', ts);

-- MySQL   (no DATE_TRUNC — format to the 1st)
SELECT DATE_FORMAT(ts, '%Y-%m-01');

-- SQLite
SELECT DATE(ts, 'start of month');

Truncate to the day / week

-- day
SELECT DATE_TRUNC('day', ts);           -- Postgres / Snowflake / DuckDB
SELECT DATE_TRUNC(ts, DAY);             -- BigQuery
SELECT DATE(ts);                        -- MySQL / SQLite

-- week (Monday start)
SELECT DATE_TRUNC('week', ts);          -- Postgres / DuckDB (ISO week, Monday)
SELECT DATE_TRUNC(ts, WEEK(MONDAY));    -- BigQuery
SELECT DATE_TRUNC('week', ts);          -- Snowflake (see WEEK_START param)

Gotchas: MySQL has no DATE_TRUNC — use DATE_FORMAT(ts, '%Y-%m-01') for month or DATE(ts) for day (cast back to DATE as needed). Week start differs: Postgres/DuckDB truncate to Monday (ISO); BigQuery defaults to Sunday unless you write WEEK(MONDAY); Snowflake follows the WEEK_START session parameter. Always pin the week start explicitly for week buckets.