mysql周week函数

WEEK(date[,mode])

WEEK()函数会返回一个日期的周数,第2个参数mode可以指定一周是从周日开始还是周一开始,以及返回值的范围是 [0, 53] 还是 [1, 53], 如果第2个参数缺失了,则使用系统变量default_week_format的值

下面这张图用来方便之后的测试

模式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

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 函数的参数类型
• 【C语言】函数的递归调用
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• NUS CS1101S：SICP JavaScript 描述：一、使用函数构建抽象
• asp.net mvc4 controller构造函数
• 函数返回值
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 10-函数进阶-作用域
• 11-函数进阶-参数类型

函数 mysql week函数mysql week 函数mysql 函数 日期mysql timestampdiff函数mysql 函数 常见 数学mysql 函数 常用 时间mysql 函数mysql count 函数 版本 方式mysql 函数 常见mysql 函数mysql出处 常见