Calculated field
YEAR([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 year(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT YEAR(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT EXTRACT(YEAR 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 | 2024 |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 2024 |
| 3 | 2025-01-01 | 2025-01-01T16:25:40 | 2025-01-01 | 2025 |
| 4 | 2025-01-31 | 2025-01-31T16:25:40 | 2025-01-31 | 2025 |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | 2025 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 2025 |
| 7 | null | null | null | null |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 2024 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- The calendar year differs from ISO week year near New Year.
Naive SQL
SELECT isoyear(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 2024 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 2025 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 2025 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 2025 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 2025 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 2025 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 2024 Matches the calculation
SELECT year(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 2024 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 2024 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 2025 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 2025 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 2025 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 2025 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 2024
Signature
YEAR(date)