Calculated field
QUARTER([shipped])
Sample data
| shipment | shipped | received | text | year | month | day | hour | minute | second | lat | lon | wkt |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2024-12-29 | 2024-12-29T16:25:40 | 2024-12-29 | 2025 | 2 | 25 | 9 | 25 | 40 | 10 | 106 | POINT (106 10) |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 2025 | 2 | 26 | 10 | 25 | 40 | 10.1 | 106.1 | POINT (106.1 10.1) |
| 3 | 2025-01-01 | 2025-01-01T16:25:40 | 2025-01-01 | 2025 | 2 | 27 | 11 | 25 | 40 | 10.2 | 106.2 | POINT (106.2 10.2) |
| 4 | 2025-01-31 | 2025-01-31T16:25:40 | 2025-01-31 | 2025 | 2 | 28 | 12 | 25 | 40 | 10.3 | 106.3 | POINT (106.3 10.3) |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | 2025 | 2 | 29 | 13 | 25 | 40 | 10.4 | 106.4 | POINT (106.4 10.4) |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 2025 | 2 | 30 | 14 | 25 | 40 | 10.5 | 106.5 | POINT (106.5 10.5) |
| 7 | null | null | null | 2025 | 2 | 31 | 15 | 25 | 40 | 10.6 | 106.6 | POINT (106.6 10.6) |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 2025 | 2 | 32 | 16 | 25 | 40 | 10.7 | 106.7 | POINT (106.7 10.7) |
The same calculation as SQL
SELECT quarter(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT QUARTER(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT EXTRACT(QUARTER FROM shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
Result
| shipment | shipped | received | text | result |
|---|---|---|---|---|
| 1 | 2024-12-29 | 2024-12-29T16:25:40 | 2024-12-29 | 4 |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 4 |
| 3 | 2025-01-01 | 2025-01-01T16:25:40 | 2025-01-01 | 1 |
| 4 | 2025-01-31 | 2025-01-31T16:25:40 | 2025-01-31 | 1 |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | 1 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 1 |
| 7 | null | null | null | null |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 1 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Quarter boundaries require subtracting one before integer division.
Naive SQL
SELECT floor(month(shipped)/3)+1 AS result FROM input ORDER BY shipment
shipment shipped received text result 1 2024-12-29 2024-12-29T16:25:40 2024-12-29 5 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 5 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 1 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 1 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 1 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 2 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 1 Matches the calculation
SELECT quarter(shipped) AS result FROM input ORDER BY shipment
shipment shipped received text result 1 2024-12-29 2024-12-29T16:25:40 2024-12-29 4 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 4 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 1 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 1 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 1 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 1 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 1
Signature
QUARTER(date)