Week numbering - what calendar is used?

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, WEEKOFYEAR and “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

We’d also like to know this, as well as how to set Sunday to the start of the week in some cases.

Currently, I work around this by pushing all date values to the client. My main client app is Superset atm. In Superset, you can use d3-time-format | D3 by Observable to format week numbers in an ISO8601 compatible way.

In Superset, to format weeknumbers, use %g for matching year, %V for week number and %u for day number matching iso 8601 week calendar: 24.17-1: %g.%V-%u

As long as I do not need to partition by week on the Dremio side, I am fine with this approach.