Calculated field
MONTH([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 month(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT MONTH(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT EXTRACT(MONTH 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 | 12 |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 12 |
| 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 | 2 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 3 |
| 7 | null | null | null | null |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 2 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Month numbers begin at one rather than zero.
Naive SQL
SELECT month(shipped)-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 11 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 11 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 0 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 0 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 month(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 12 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 12 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 2 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 3 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 2
Signature
MONTH(date)