Calculated field
ISOYEAR([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 isoyear(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT YEAROFWEEKISO(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT EXTRACT(ISOYEAR 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 | 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 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- ISO year can advance before the calendar year ends.
Naive SQL
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 Matches the calculation
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
Signature
ISOYEAR(date)