Pulls a single part out of a date or time: the year, month, day, hour.
EXTRACT(<field> FROM <date or time>)
Returns a number: YEAR — the year, MONTH — the month from 1 to 12, DAY — the day of the month, HOUR — the hour, DOW — the day of the week. The field is written without quotes. Handy for filtering or grouping by part of a date: orders before noon, sales by hour.
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.
| year | month | day |
|---|---|---|
| 2026 | 9 | 13 |
| id | created_at | month |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 7 |
| 2 | 2026-07-28 18:40:00 | 7 |
| 3 | 2026-08-09 09:05:00 | 8 |
| 4 | 2026-08-21 14:30:00 | 8 |
| 5 | 2026-09-02 11:00:00 | 9 |
| id | created_at |
|---|---|
| 1 | 2026-07-03 10:15:00 |
| 3 | 2026-08-09 09:05:00 |
| 5 | 2026-09-02 11:00:00 |
The result is a numeric, so seconds can have a fractional part: EXTRACT(SECOND FROM TIMESTAMP '2026-09-13 15:47:30.5') gives 30.500000.
DOW counts Sunday as 0 and Monday as 1. For the order where Monday is 1 and Sunday is 7, use ISODOW.
Grouping by EXTRACT(MONTH FROM …) merges the same month of different years into one row. For monthly reports, use DATE_TRUNC('month', …).