DATE_TRUNC

Overview

DATE_TRUNC truncates a date, timestamp, or interval to the beginning of a specified precision: year, quarter, month, week, day, hour, minute, second, and more. It's the standard way to bucket raw event timestamps into daily, weekly, or monthly aggregates.

Copy code
SELECT
  DATE_TRUNC('month', order_timestamp) AS order_month,
  SUM(revenue) AS monthly_revenue
FROM orders
GROUP BY ALL
ORDER BY ALL;

DATE_TRUNC('week', ts) truncates to the most recent Monday (ISO week start), while DATE_TRUNC('day', ts) zeroes out the time-of-day portion, leaving midnight.

DATE_TRUNC vs EXTRACT

DATE_TRUNC returns a value of the same type (date/timestamp) rounded down — useful for grouping and joining on time buckets. EXTRACT instead pulls out a single numeric field (like the month number) without preserving the full date. Use DATE_TRUNC when you need to group by a time period and still display or compare it as a date; use EXTRACT when you only need the bare number.

DuckDB specifics

DuckDB implements date_trunc('part', value) matching Postgres semantics, and also accepts the alias datetrunc(). It supports the same argument order and part names ('year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'milliseconds', 'microseconds', 'decade', 'century'), and works over DATE, TIMESTAMP, and TIMESTAMPTZ columns. Combined with DuckDB's GROUP BY ALL, monthly rollups become compact:

Copy code
SELECT DATE_TRUNC('day', event_time) AS day, COUNT(*) AS events
FROM events
GROUP BY ALL
ORDER BY ALL;