mysql周week函数

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

下面这张图用来方便之后的测试
1月1号日历

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

获取当前日期的周一
用当前日期减去当前日期的周序号就是周一的日期
SELECT DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY);

mysql> SELECT DATE_SUB('2024-01-05', INTERVAL WEEKDAY('2020-01-05') DAY);
+------------------------------------------------------------+
| DATE_SUB('2024-01-05', INTERVAL WEEKDAY('2020-01-05') DAY) |
+------------------------------------------------------------+
| 2023-12-30                                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFWEEK(date)

返回日期的周的序号, 1:Sunday, 2:Monday, ..., 7:Saturday,

mysql> select dayofweek('2023-01-01'), dayofweek('2023-01-02'), dayofweek('2023-01-07');
+-------------------------+-------------------------+-------------------------+
| dayofweek('2023-01-01') | dayofweek('2023-01-02') | dayofweek('2023-01-07') |
+-------------------------+-------------------------+-------------------------+
|                       1 |                       2 |                       7 |
+-------------------------+-------------------------+-------------------------+

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 |
+-----------------------+---------------------------+

DATE_FORMAT(date,format)

DATE_FORMAT(date,'%x%v') 类似于 YEARWEEK(date, 3);
DATE_FORMAT(date,'%X%V') 类似于 YEARWEEK(date, 2);

mysql> select DATE_FORMAT('2023-01-01','%x%v'), yearweek('2023-01-01', 3);
+----------------------------------+---------------------------+
| DATE_FORMAT('2023-01-01','%x%v') | yearweek('2023-01-01', 3) |
+----------------------------------+---------------------------+
| 202252                           |                    202252 |
+----------------------------------+---------------------------+

mysql> select DATE_FORMAT('2023-01-01','%X%V'), yearweek('2023-01-01', 2);
+----------------------------------+---------------------------+
| DATE_FORMAT('2023-01-01','%X%V') | yearweek('2023-01-01', 2) |
+----------------------------------+---------------------------+
| 202301                           |                    202301 |
+----------------------------------+---------------------------+

DATE_FORMAT的占位符说明:

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

上表中有2个 %x,最后一个%x指如果占位符不是上面任何一个的话,则原样展示

mysql> select DATE_FORMAT('2023-01-01','%q'), DATE_FORMAT('2023-01-01','%你');
+--------------------------------+----------------------------------+
| DATE_FORMAT('2023-01-01','%q') | DATE_FORMAT('2023-01-01','%你')  |
+--------------------------------+----------------------------------+
| q                              | 你                               |
+--------------------------------+----------------------------------+

参考

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

posted @ 2023-09-02 14:54 theSummerDay 阅读(2933) 评论(0) 编辑收藏举报

刷新页面返回顶部

qode

mysql周week函数

WEEK(date[,`mode`])

模式0

模式1

模式3

模式5

模式7

系统变量`default_week_format`

WEEKDAY(date)

DAYOFWEEK(date)

WEEKOFYEAR(date)

YEARWEEK(date), YEARWEEK(date,mode)

DATE_FORMAT(date,format)

参考

公告

qode

mysql周week函数

WEEK(date[,mode])

模式0

模式1

模式3

模式5

模式7

系统变量default_week_format

WEEKDAY(date)

DAYOFWEEK(date)

WEEKOFYEAR(date)

YEARWEEK(date), YEARWEEK(date,mode)

DATE_FORMAT(date,format)

参考

公告

WEEK(date[,`mode`])

系统变量`default_week_format`