Calculated field
ISOWEEK([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 week(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT WEEKISO(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT EXTRACT(ISOWEEK 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 | 52 |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 1 |
| 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 | 5 |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | 9 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 11 |
| 7 | null | null | null | null |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 9 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- ISO weeks differ from calendar Sunday-based weeks.
Naive SQL
SELECT CAST(floor((date_diff('day',make_date(year(shipped)::INTEGER,1,1),shipped)+dayofweek(make_date(year(shipped)::INTEGER,1,1)))/7)+1 AS BIGINT) AS result FROM input ORDER BY shipmentshipment shipped received text result 1 2024-12-29 2024-12-29T16:25:40 2024-12-29 53 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 53 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 5 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 9 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 11 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 9 Matches the calculation
SELECT week(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 52 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 1 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 5 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 9 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 11 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 9
Signature
ISOWEEK(date)