@andyyzh what's the usecase for this? is it somehow related to Lunar calendar?

@andyyzh what's the usecase for this? is it somehow related to Lunar calendar?

A week number computing rule: the week contained January 1st is the first week, contained a day to make configurable.
It has nothing to do with the lunar calendar.

USA, Canada, most of Latin America, Japan, Israel, South Korea, among others, use a week numbering system (called North American in our Calculator) in which the first week (numbered 1) of any given year is the week which contains January 1st.
https://www.calendar-12.com/week_number

I'm trying to figure out the motivation for this implementation. (Sorry, it doesn't look clear for me.)

It looks like that the initial motivation is to provide week numbering to start first week from January 1st.

But you probably have noticed that the ISO week numbering starts from the first week containing January 4th. And you wanted to generalize this function, so you've added the possibility to start the numbering from any selected day within a year. Is this really needed?

I'm asking because it looks like a false generalization. ISO week numbering starts from the first week containing January 4th, because, coincidentally, it is also the first week that contains majority of days (at least 4 of 7) within a year.

And the possibility to start from the first week containing January 3rd or January 5th doesn't make much sense. It is still can make sense to start from a week containing January 1st (first week with at least one day within year) or January 7th (first full week within a year).

But I wonder if it is motivated by some unusual local calendars...

For week numbering system, at least the following customizations make sense:

• start a week from Monday (as in Europe) or Sunday (as in USA) or Saturday;
• allow numbered week to cross the year boundary (as in ISO-8601); or reset the numbering at January 1st; or use number zero instead of one if a week belongs to previous year;
• how to select the first week of a year: by first day of year or by majority of days within a year (ISO-8601) or containing completely within a year.

For example of (still incomplete) generalization of week numbering system, look at:
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week
and
https://en.wikipedia.org/wiki/Week#Week_numbering

But you probably have noticed that the ISO week numbering starts from the first week containing January 4th. And you wanted to generalize this function, so you've added the possibility to start the numbering from any selected day within a year. Is this really needed?
toCustomWeek(DateTime, [, CustomDate][, Timezone])

Adding 'CustomDate' parameter is unnecessary, it's to make functions more general.

But I wonder if it is motivated by some unusual local calendars...

No special calendar requirement,

I plan to delete the 'CustomDate' parameter to make the purpose of the function clearer.

Do you want to change the name of the function? called NorthWeek or USWeek?

I plan to delete the 'CustomDate' parameter to make the purpose of the function clearer.
Do you want to change the name of the function? called NorthWeek or USWeek?

Ok. Yes, better to change the name.
USWeek is not appropriate, because weeks start at Sunday in US.
NorthWeek is too confusing.

Better to implement the week function from MySQL (at least for compatibility).
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

Prepare to add these functions that similar to mysql, and add new mode:

WEEK(date[,mode])
YEAR(date[,mode])
YEARWEEK(date[,mode])

I'll commit it in two weeks.

Ok.

Implementing functions similar to those in MySQL;

week(date[,mode])

This function returns the week number for date or datetime. The two-argument form of week() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the default mode is 0.
The following table describes how the mode argument works.

For mode values with a meaning of “with 4 or more days this year,” weeks are numbered according to ISO 8601:1988:

• If the week containing January 1 has 4 or more days in the new year, it is week 1.

• Otherwise, it is the last week of the previous year, and the next week is week 1.

For mode values with a meaning of “contains January 1”, the week contains January 1 is week 1. It doesn't matter how many days in the new year the week contained, even if it contained only one day.

week(DateTime1, [, mode][, Timezone])

Parameters

• DateTime1 – Date or DateTime.
• mode – Optional parameter, Range of values is [0,9], default is 0.
• Timezone – Optional parameter, it behaves like any other conversion function.

Example

SELECT toDate('2016-12-27') AS date, week(date) AS week0, week(date,1) AS week1, week(date,9) AS week9;

┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘

yearWeek(date[,mode])

Returns year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year.

The mode argument works exactly like the mode argument to week(). For the single-argument syntax, a mode value of 0 is used.

Example

SELECT toDate('2016-12-27') AS date, yearWeek(date) AS yearWeek0, yearWeek(date,1) AS yearWeek1, yearWeek(date,9) AS yearWeek9;

┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┐
│ 2016-12-27 │    201652 │    201652 │    201701 │
└────────────┴───────────┴───────────┴───────────┘

@alexey-milovidov
Does default mode value need to be added to the system settings?
FYI:
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_default_week_format

@vitlibar , I'm done. please review code, thank you.

All checks have passed.

All previous tests have passed. I don't think the failing tests are caused by my change.
@alexey-milovidov If you have time, take a look.

@vitlibar
All the review issues have been fixed except toStartOfWeekInterval() function was not added.
Please review again.

@vitlibar went on vacation. I will reassign this task to someone else.

@vitlibar went on vacation. I will reassign this task to someone else.

ok, thanks!