week starting from sunday

[tiboun]

Duckdb currently support ISOWEEK only from date extraction. We can support week that start on first sunday thanks to the time formatting.

WITH t AS (SELECT CAST('2017-11-05' AS DATE) AS date)
SELECT
  date,
  CAST(strftime("date", '%U')  AS INT) AS week_sunday,
  EXTRACT(WEEK FROM date) AS week_monday FROM t

What do you think about implementing this ?

[manticore-projects]

Greetings @tiboun,

as far as I understand it, ISO_WEEK relates to the number of weeks in the year, but not to counting Sunday/Monday (although ISO Weeks start with Monday also).

I think what you are referring to is WEEK(<WEEKDAY>) and this can indeed be taken from StrFTime -- but only for"

1. EXTRACT (not for DATE_DIFF etc.)
2. only for SUNDAY vs. MONDAY

So, additionally supported Date Parts for EXTRACT would be: WEEK, WEEK(SUNDAY), WEEK(MONDAY)?

[tiboun]

Hi @manticore-projects , here is the definition of ISO_WEEK

Week number according to the ISO-8601 standard, weeks starting on Monday. The first week of the year is the week that contains that year's first Thursday (='First 4-day week'). ISO representation: 2024-W24

I don't think that we can suppose that week(monday) <=> iso_week. This is an example:

select extract(week(monday) from date '2023-01-01') , extract(isoweek from date '2023-01-01')

Row	f0_	f1_
1	0	52

We don't get the same result. So what we may support is the usage of WEEK, which is the same as WEEK(SUNDAY).

select d, extract(week(sunday) from d) , extract(week from d) from unnest(generate_date_array(date '2000-01-01', current_date(), interval 1 year)) d
order by d

[manticore-projects]

Yes, that was essentially what I was saying:

a) we can't really support ISO_WEEK because right now DuckDB has not support for it (unless we want to provide a patch for DuckDB). We can make it work maybe for EXTRACT only and I will look into this.

b) we can support 'WEEK', 'WEEK(MONDAY)' and 'WEEK(SUNDAY)' maybe, but again only for EXTRACT.

I may try to check the DuckDB sources for this, its an area where the Transpiler alone can't win.

[tiboun]

totally agree with you :)