Calculated field
DAY([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 day(shipped) AS result FROM input ORDER BY shipment
Runs the example above.
SELECT DAY(shipped) AS result FROM input ORDER BY shipment
Written for this dialect; not run here.
SELECT EXTRACT(DAY 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 | 29 |
| 2 | 2024-12-30 | 2024-12-30T16:25:40 | 2024-12-30 | 30 |
| 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 | 31 |
| 5 | 2025-02-28 | 2025-02-28T16:25:40 | 2025-02-28 | 28 |
| 6 | 2025-03-15 | 2025-03-15T16:25:40 | 2025-03-15 | 15 |
| 7 | null | null | null | null |
| 8 | 2024-02-29 | 2024-02-29T16:25:40 | 2024-02-29 | 29 |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Day of month differs from day of week.
Naive SQL
SELECT dayofweek(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 0 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 Matches the calculation
SELECT day(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 29 2 2024-12-30 2024-12-30T16:25:40 2024-12-30 30 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 31 5 2025-02-28 2025-02-28T16:25:40 2025-02-28 28 6 2025-03-15 2025-03-15T16:25:40 2025-03-15 15 7 null null null null 8 2024-02-29 2024-02-29T16:25:40 2024-02-29 29
Signature
DAY(date)