Week numbering - what calendar is used?

1

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

2

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

3

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

4

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

5

Dremio calculates WEEK OF YEAR using ISO 8601 standards which follows these rules:

Weeks start with Monday and end on Sunday. Each week’s year is the Gregorian year in which the Thursday falls. The first week of the year, hence, always contains 4 January. ISO week year numbering therefore usually deviates by 1 from the Gregorian for some days close to 1 January.

Dremio doesn’t seem to expose a YEAR OF WEEK function to return the week’s year according to ISO 8601 standard.

Most businesses in the US like their week numbers to follow these rules:

  • January 1st of any year always starts with Week 1
  • First day of week is Monday and not Sunday

You can achieve this with a Dremio User Defined Function like this:

CREATE OR replace FUNCTION udf.week_of_year_us(input date)
returns int
RETURN
(
       SELECT 1 + (Dayofyear(input) +
              CASE
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '1' THEN 5
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '2' THEN 0
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '3' THEN 0
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '4' THEN 1
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '5' THEN 2
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '6' THEN 3
                     WHEN Dayofweek(Date_trunc('YEAR',input)) = '7' THEN 4
              END ) / 7 AS woy_us);

Usage Examples:

select WEEKOFYEAR(cast('2022-12-31' as date)), udf.week_of_year_us(cast('2022-12-31' as date)); // Returns 52, 53
select WEEKOFYEAR(cast('2023-01-01' as date)), udf.week_of_year_us(cast('2023-01-01' as date)); // Return 52, 1
select WEEKOFYEAR(cast('2023-01-02' as date)), udf.week_of_year_us(cast('2023-01-02' as date)); // Returns 1 , 2 (2023-01-02 is a Monday)
6

Thank you for sharing @Benny_Chow.