WEEK(date[,mode])
WEEK()函数会返回一个日期的周数,第2个参数mode可以指定一周是从周日开始还是周一开始,以及返回值的范围是 [0, 53] 还是 [1, 53], 如果第2个参数缺失了,则使用系统变量default_week_format的值
| Mode | First day of week | Range | Week 1 is the first week … |
|---|---|---|---|
| 0 | Sunday | 0-53 | with a Sunday in this year |
| 1 | Monday | 0-53 | with 4 or more days this year |
| 2 | Sunday | 1-53 | with a Sunday in this year |
| 3 | Monday | 1-53 | with 4 or more days this year |
| 4 | Sunday | 0-53 | with 4 or more days this year |
| 5 | Monday | 0-53 | with a Monday in this year |
| 6 | Sunday | 1-53 | with 4 or more days this year |
| 7 | Monday | 1-53 | with a Monday in this year |
下面这张图用来方便之后的测试
模式0
由于default_week_format默认为0,所以模式0为默认模式
一周的第一天是周日,第一周是今年第一个有周日的周,第一周之前今年的日期为第0周
mysql> select week('2020-01-01', 0), week('2020-01-05', 0);
+-----------------------+-----------------------+
| week('2020-01-01', 0) | week('2020-01-05', 0) |
+-----------------------+-----------------------+
| 0 | 1 |
+-----------------------+-----------------------+
模式1
一周的第一天是周一,第一周是今年第一个有4天或4天以上的周,第一周之前的今年的日期为第0周
mysql> select week('2020-01-01', 1), week('2021-01-01', 1);
+-----------------------+-----------------------+
| week('2020-01-01', 1) | week('2021-01-01', 1) |
+-----------------------+-----------------------+
| 1 | 0 |
+-----------------------+-----------------------+
模式3
一周的第一天是周一,第一周是今年第一个有4天或4天以上的周,第一周之前的今年的日期为上一年的第52/53周,即最后一周
mysql> select week('2020-01-01', 3), week('2021-01-01', 3), week('2022-01-01', 3);
+-----------------------+-----------------------+-----------------------+
| week('2020-01-01', 3) | week('2021-01-01', 3) | week('2022-01-01', 3) |
+-----------------------+-----------------------+-----------------------+
| 1 | 53 | 52 |
+-----------------------+-----------------------+-----------------------+
mysql> select yearweek('2020-01-01', 3), yearweek('2021-01-01', 3), yearweek('2022-01-01', 3);
+---------------------------+---------------------------+---------------------------+
| yearweek('2020-01-01', 3) | yearweek('2021-01-01', 3) | yearweek('2022-01-01', 3) |
+---------------------------+---------------------------+---------------------------+
| 202001 | 202053 | 202152 |
+---------------------------+---------------------------+---------------------------+
模式5
一周的第一天是周一,第一周是今年第一个有星期一的周,第一周之前的今年的日期为第0周
mysql> select week('2020-01-01', 5), week('2024-01-01', 5);
+-----------------------+-----------------------+
| week('2020-01-01', 5) | week('2024-01-01', 5) |
+-----------------------+-----------------------+
| 0 | 1 |
+-----------------------+-----------------------+
模式7
一周的第一天是周一,第一周是今年第一个有星期一的周,第一周之前的今年的日期为上一年的第52或53周,即最后一周
mysql> select week('2019-01-01', 7), week('2020-01-01', 7), week('2024-01-01', 7);
+-----------------------+-----------------------+-----------------------+
| week('2019-01-01', 7) | week('2020-01-01', 7) | week('2024-01-01', 7) |
+-----------------------+-----------------------+-----------------------+
| 53 | 52 | 1 |
+-----------------------+-----------------------+-----------------------+
mysql> select yearweek('2019-01-01', 7), yearweek('2020-01-01', 7), yearweek('2024-01-01', 7);
+---------------------------+---------------------------+---------------------------+
| yearweek('2019-01-01', 7) | yearweek('2020-01-01', 7) | yearweek('2024-01-01', 7) |
+---------------------------+---------------------------+---------------------------+
| 201853 | 201952 | 202401 |
+---------------------------+---------------------------+---------------------------+
系统变量default_week_format
Integer类型,默认值为0,范围为[0, 7]
WEEKDAY(date)
返回日期的周的序号, 0:Monday, ..., 6:Sunday
mysql> select weekday('2023-01-01'), weekday('2024-01-01');
+-----------------------+-----------------------+
| weekday('2023-01-01') | weekday('2024-01-01') |
+-----------------------+-----------------------+
| 6 | 0 |
+-----------------------+-----------------------+
WEEKOFYEAR(date)
相当于 WEEK(date,3)
mysql> select week('2023-01-01', 3), weekofyear('2023-01-01'), week('2024-01-01', 3), weekofyear('2024-01-01');
+-----------------------+--------------------------+-----------------------+--------------------------+
| week('2023-01-01', 3) | weekofyear('2023-01-01') | week('2024-01-01', 3) | weekofyear('2024-01-01') |
+-----------------------+--------------------------+-----------------------+--------------------------+
| 52 | 52 | 1 | 1 |
+-----------------------+--------------------------+-----------------------+--------------------------+
YEARWEEK(date), YEARWEEK(date,mode)
返回日期的年和周, 其参数mode和WEEK()函数中的mode的含义完全相同
和WEEK()不同的是: 如果缺失参数mode时,默认值为0,不受系统变量default_week_format的影响
# `default_week_format` 默认值为0
mysql> show variables like '%default_week_format%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| default_week_format | 0 |
+---------------------+-------+
# 在`mode`缺失和等于1的情况下, 查看 2024-01-01 的`WEEK()`和`YEARWEEK()`的结果
mysql> select week('2024-01-01'), week('2024-01-01', 7), yearweek('2024-01-01'), yearweek('2024-01-01', 7);
+--------------------+-----------------------+------------------------+---------------------------+
| week('2024-01-01') | week('2024-01-01', 7) | yearweek('2024-01-01') | yearweek('2024-01-01', 7) |
+--------------------+-----------------------+------------------------+---------------------------+
| 0 | 1 | 202353 | 202401 |
+--------------------+-----------------------+------------------------+---------------------------+
# 修改`default_week_format`的值为7
mysql> set default_week_format=7;
Query OK, 0 rows affected (0.00 sec)
# 检查`default_week_format`的值已经修改为7
mysql> show variables like '%default_week_format%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| default_week_format | 7 |
+---------------------+-------+
# 再次查询 2024-01-01 的结果, 发现在缺失`mode`的情况下: `WEEK()`的值已经从之前的0变为1和模式7的结果相同,但`YEARWEEK()`的结果没有变化
mysql> select week('2024-01-01'), week('2024-01-01', 7), yearweek('2024-01-01'), yearweek('2024-01-01', 7);
+--------------------+-----------------------+------------------------+---------------------------+
| week('2024-01-01') | week('2024-01-01', 7) | yearweek('2024-01-01') | yearweek('2024-01-01', 7) |
+--------------------+-----------------------+------------------------+---------------------------+
| 1 | 1 | 202353 | 202401 |
+--------------------+-----------------------+------------------------+---------------------------+
注意:返回结果中的年不一定和日期中的年相同, 结果中的周也不一定和WEEK()返回的周相同
mysql> select week('2020-01-01', 0), yearweek('2020-01-01', 0);
+-----------------------+---------------------------+
| week('2020-01-01', 0) | yearweek('2020-01-01', 0) |
+-----------------------+---------------------------+
| 0 | 201952 |
+-----------------------+---------------------------+
参考
MySQL日期时间函数
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week
MySQL系统变量
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_default_week_format