Week numbering sounds simple, but as most calendar-related topics it is not,
e.g. see wikipedia on the ISO week date.
Week numbering is very important to our customers.
I know I can defer to the native functionality of my databases.
But does Dremio comply with ISO 8601 for week numbering?
The Date/Time documentation is not explicit about this:
| Function | Description |
|---|---|
| WEEK | Extracts the week number (from 0 to 53) for a given date or timestamp. |
| WEEKOFYEAR | Returns the week of year of the date or timestamp. |
And the format string function appear to be different:
| Format Element | Description | Example |
|---|---|---|
| WW | Week of year (0-52) | 4, 43 |
(My tests show 1-53 and 04.)
Some questions:
- What is the difference between
WEEK,WEEKOFYEARand “WW” format string? - How to specify Monday as the first day of the week?
- How to get the year with the week number (they can differ from the Gregorian calendar year around New Year)?
- Does Dremio take any client settings into account?
Example dates, taken from wikipedia on the ISO week date:
| date_value | date_label | iso_week_date_label | extract_week_number | weekofyear_function | week_number_ww | weekday | day_of_week_d |
|---|---|---|---|---|---|---|---|
| 1977-01-01 | Sat 1 Jan 1977 | 1976-W53-6 | 53 | 53 | 53 | 7 | 7 |
| 1977-01-02 | Sun 2 Jan 1977 | 1976-W53-7 | 53 | 53 | 53 | 1 | 1 |
| 1977-12-31 | Sat 31 Dec 1977 | 1977-W52-6 | 52 | 52 | 52 | 7 | 7 |
| 1978-01-01 | Sun 1 Jan 1978 | 1977-W52-7 | 52 | 52 | 52 | 1 | 1 |
| 1978-01-02 | Mon 2 Jan 1978 | 1978-W01-1 | 1 | 1 | 01 | 2 | 2 |
| 1978-12-31 | Sun 31 Dec 1978 | 1978-W52-7 | 52 | 52 | 52 | 1 | 1 |
| 1979-01-01 | Mon 1 Jan 1979 | 1979-W01-1 | 1 | 1 | 01 | 2 | 2 |
| 1979-12-30 | Sun 30 Dec 1979 | 1979-W52-7 | 52 | 52 | 52 | 1 | 1 |
| 1979-12-31 | Mon 31 Dec 1979 | 1980-W01-1 | 1 | 1 | 01 | 2 | 2 |
| 1980-01-01 | Tue 1 Jan 1980 | 1980-W01-2 | 1 | 1 | 01 | 3 | 3 |
| 1980-12-28 | Sun 28 Dec 1980 | 1980-W52-7 | 52 | 52 | 52 | 1 | 1 |
| 1980-12-29 | Mon 29 Dec 1980 | 1981-W01-1 | 1 | 1 | 01 | 2 | 2 |
| 1980-12-30 | Tue 30 Dec 1980 | 1981-W01-2 | 1 | 1 | 01 | 3 | 3 |
| 1980-12-31 | Wed 31 Dec 1980 | 1981-W01-3 | 1 | 1 | 01 | 4 | 4 |
| 1981-01-01 | Thu 1 Jan 1981 | 1981-W01-4 | 1 | 1 | 01 | 5 | 5 |
| 1981-12-31 | Thu 31 Dec 1981 | 1981-W53-4 | 53 | 53 | 53 | 5 | 5 |
| 1982-01-01 | Fri 1 Jan 1982 | 1981-W53-5 | 53 | 53 | 53 | 6 | 6 |
| 1982-01-02 | Sat 2 Jan 1982 | 1981-W53-6 | 53 | 53 | 53 | 7 | 7 |
| 1982-01-03 | Sun 3 Jan 1982 | 1981-W53-7 | 53 | 53 | 53 | 1 | 1 |
I use the base image dremio/dremio-oss:24.3.2