Calculated field
ISOWEEKDAY([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 isodow(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT DAYOFWEEKISO(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT MOD(EXTRACT(DAYOFWEEK FROM shipped)+5,7)+1 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 | 7 |
| 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 | 3 |
| 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 | 5 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 6 |
| 7 | null | null | null | null |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 4 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- ISO weekdays start on Monday, not Sunday.
Naive SQL
SELECT dayofweek(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 1 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 2 3 2025-01-01 2025-01-01T16:25:40 2025-01-01 4 4 2025-01-31 2025-01-31T16:25:40 2025-01-31 6 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 6 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 7 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 5 Matches the calculation
SELECT isodow(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 7 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 3 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 5 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 6 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 4
Signature
ISOWEEKDAY(date)