DATE_TRUNC
Round a timestamp down to a coarser granularity (hour, day, week, month, quarter, year). Essential for time-series rollups: group by DATE_TRUNC('month', ts) and each row lands in the first day of its month at 00:00:00.
Syntax #
sql
SELECT DATE_TRUNC('month', ts_col) AS month, COUNT(*)
FROM table_name
GROUP BY month
ORDER BY month;Dialect comparison #
| Engine | Syntax | Gotcha |
|---|---|---|
| DuckDB / PostgreSQL / Redshift / Snowflake | DATE_TRUNC('month', col) | unit is a quoted string |
| BigQuery | TIMESTAMP_TRUNC(col, MONTH) for TIMESTAMP; DATETIME_TRUNC for DATETIME; DATE_TRUNC(col, MONTH) for DATE | argument order reversed; unit unquoted; pick the function that matches your column type |
| SQL Server 2022+ | DATETRUNC(month, col) | no underscore; unit unquoted |
| MySQL | CAST(DATE_FORMAT(col, '%Y-%m-01') AS DATE) | no native DATE_TRUNC |
Example #
Loading SQL editor...
Info
DATE_TRUNC in WHERE disables index usage. WHERE DATE_TRUNC('month', order_date) = '2024-01-01' forces the engine to evaluate the function on every row — any index on order_date is bypassed. Rewrite as a half-open range: WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'. Same result, indexable, and unambiguous about the interval boundaries. The same pattern applies to YEAR(col), MONTH(col), EXTRACT(...), and any function wrapping an indexed column in a predicate.