date-time
DATE_TRUNC
Rounds a timestamp down to the beginning of the specified unit (year, month, week, day, hour, etc.). Native to Postgres; MySQL uses DATE_FORMAT or manual arithmetic.
DATE_TRUNC('unit', timestamp)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| 'year' | 'quarter' | 'month' | 'week' | 'day' | 'hour' | 'minute' | Truncation granularity |
| AT TIME ZONE 'zone' | Truncate in a specific timezone before returning UTC |
| DATE_FORMAT(dt, fmt) | MySQL fallback used to emulate DATE_TRUNC via format strings |
Examples
SELECT DATE_TRUNC('month', NOW());Postgres: first instant of the current month
SELECT DATE_TRUNC('day', created_at), COUNT(*) FROM events GROUP BY 1;Daily event totals bucketed by day
SELECT DATE_FORMAT(created_at, '%Y-%m-01') FROM events;MySQL equivalent for month truncation, returned as a date string
SELECT DATE_TRUNC('hour', ts AT TIME ZONE 'Asia/Kolkata') FROM logs;Truncate to hour boundaries in IST rather than UTC
Dialect notes / Gotcha
Postgres-only function — MySQL/SQLite require DATE_FORMAT or strftime. Weeks start on Monday in Postgres (ISO), which may differ from application definitions.
Related functions
EXTRACT / DATE_PART
Pulls a numeric component (year, month, day, hour, minute, dow, epoch) out of a date or timestamp. EXTRACT is SQL-standard and works in Postgres and MySQL; DATE_PART is a Postgres synonym.
NOW / CURRENT_TIMESTAMP
Returns the current transaction (or statement) timestamp. Both spellings work in Postgres and MySQL; SQLite prefers CURRENT_TIMESTAMP or datetime('now').
INTERVAL arithmetic
Adds or subtracts a duration to a date/timestamp. Postgres uses the SQL-standard INTERVAL literal directly; MySQL wraps it in DATE_ADD/DATE_SUB; SQLite uses date modifiers.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet