week iso related function

[wangsimo0]

As mentioned in #15385, the week function in sr is not following ISO week.

There are two week related function needs week_iso function:

1. weekiso: this is an iso version of weekofyear
2. dayofweekiso: this is an iso version of dayofweek

[qmengss]

Hi，could you assign this task to me please?

[qmengss]

My idea is to implement the weekiso function, which requires taking Monday as the start of each week. Originally, I thought about implementing it simply by calling week_of_year_with_mode directly. However, this results in the following output for the weekiso function:

mysql> select week(event_time),week_iso(event_time),event_time from detail where event_time>'2023-01-01 00:00:00';
+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                   0 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

However, this does not seem to be compliant with ISO. Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year, and the output should be as follows:

+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                  52 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

Do you think this logic is feasible? @wangsimo0

[LiShuMing]

Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year

My idea is to implement the weekiso function, which requires taking Monday as the start of each week. Originally, I thought about implementing it simply by calling week_of_year_with_mode directly. However, this results in the following output for the weekiso function:

mysql> select week(event_time),week_iso(event_time),event_time from detail where event_time>'2023-01-01 00:00:00';
+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                   0 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

However, this does not seem to be compliant with ISO. Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year, and the output should be as follows:

+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                  52 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

Do you think this logic is feasible? @wangsimo0

Sounds great. Can you pull a request for your anlysis?

[qmengss]

Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year

My idea is to implement the weekiso function, which requires taking Monday as the start of each week. Originally, I thought about implementing it simply by calling week_of_year_with_mode directly. However, this results in the following output for the weekiso function:

mysql> select week(event_time),week_iso(event_time),event_time from detail where event_time>'2023-01-01 00:00:00';
+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                   0 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

However, this does not seem to be compliant with ISO. Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year, and the output should be as follows:

+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                  52 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

Do you think this logic is feasible? @wangsimo0

Sounds great. Can you pull a request for your anlysis?

hi, this is my PR, #22773.

[qmengss]

Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year

My idea is to implement the weekiso function, which requires taking Monday as the start of each week. Originally, I thought about implementing it simply by calling week_of_year_with_mode directly. However, this results in the following output for the weekiso function:

mysql> select week(event_time),week_iso(event_time),event_time from detail where event_time>'2023-01-01 00:00:00';
+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                   0 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

However, this does not seem to be compliant with ISO. Therefore, when the week_of_year_with_mode function is called with a date and a parameter of 1, we need to recalculate using the last day of the previous year as the parameter when the return value is 0. We should get 52 weeks for the previous year, and the output should be as follows:

+------------------+---------------------+---------------------+
| week(event_time) | week_iso(event_time)| event_time          |
+------------------+---------------------+---------------------+
|                1 |                   1 | 2023-01-05 00:05:00 |
|                2 |                   2 | 2023-01-09 00:09:00 |
|                1 |                   1 | 2023-01-02 00:02:00 |
|                1 |                   1 | 2023-01-06 00:06:00 |
|                1 |                   1 | 2023-01-03 00:03:00 |
|                1 |                   1 | 2023-01-07 00:07:00 |
|                2 |                   2 | 2023-01-10 00:10:00 |
|                1 |                  52 | 2023-01-01 00:01:00 |
|                1 |                   1 | 2023-01-04 00:04:00 |
|                2 |                   1 | 2023-01-08 00:08:00 |
+------------------+---------------------+---------------------+

Do you think this logic is feasible? @wangsimo0

Sounds great. Can you pull a request for your anlysis?

Hi,I found that when calling week(datetime, mode) with mode set to 3, it conforms to the ISO week standard. So I simplified the logic by directly calling the underlying method of week with mode set to 3. This reduces the amount of code and achieves the same effect.