Truncates a date and time to the start of the year, month, week, day or hour.
DATE_TRUNC('<unit>', <timestamp>)
Drops everything smaller than the given unit: 'month' turns any moment of a month into its first day at 00:00. Units: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute'. Most often used to group data by month or day.
orders
| id | created_at | amount |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 120.50 |
| 2 | 2026-07-28 18:40:00 | 80.25 |
| 3 | 2026-08-09 09:05:00 | 199.99 |
| 4 | 2026-08-21 14:30:00 | 45.00 |
| 5 | 2026-09-02 11:00:00 | 64.75 |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| month_start | day_start | hour_start |
|---|---|---|
| 2026-09-01 00:00:00 | 2026-09-13 00:00:00 | 2026-09-13 15:00:00 |
| id | created_at | month |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 2026-07-01 00:00:00 |
| 2 | 2026-07-28 18:40:00 | 2026-07-01 00:00:00 |
| 3 | 2026-08-09 09:05:00 | 2026-08-01 00:00:00 |
| 4 | 2026-08-21 14:30:00 | 2026-08-01 00:00:00 |
| 5 | 2026-09-02 11:00:00 | 2026-09-01 00:00:00 |
| month | order_count | revenue |
|---|---|---|
| 2026-07-01 00:00:00 | 2 | 200.75 |
| 2026-08-01 00:00:00 | 2 | 244.99 |
| 2026-09-01 00:00:00 | 1 | 64.75 |
A week starts on Monday: DATE_TRUNC('week', …) for Sunday, September 13 returns Monday, September 7.
If you pass a date, the result is timestamp with time zone, and how it looks depends on the time zone. If you need a plain date, add ::date: DATE_TRUNC('month', DATE '2026-09-13')::date.
To group by month, use DATE_TRUNC, not EXTRACT(MONTH FROM …): otherwise September of different years merges into a single row.