转：https://blog.csdn.net/weixin_36099799/article/details/113417898
需求描述:
在编写SQL的时候,有这么个需求,就是要查出来某个日期所在的周一是几号,进行了测试,在此进行记录下.
测试过程:
议题:查看某个日期所在的周一是几号
分析:如果某个日期是周一,那么加上整数周之后的日期还是周一,所以,想要查看某个日期所在的周一是几号,就要知道这个日期和某个周一之间相差的整数周就可以了.
1.首先找到某个星期一,以1970.01.05这个日期为例.查看这个日期是周几?
mysql> select date_format('1970-01-05','%W');+--------------------------------+
| date_format('1970-01-05','%W') |
+--------------------------------+
| Monday |
+--------------------------------+
1 row in set (0.00 sec)
备注:通过查看的结果,可以知道,1970年1月5号是周一,所以使用这个日期就能知道某个日期所在的周一是几号了.
2.查看这个日期和某个日期之间差多少天
mysql> select datediff('2018-06-26','1970-01-05');+-------------------------------------+
| datediff('2018-06-26','1970-01-05') |
+-------------------------------------+
| 17704 |
+-------------------------------------+
1 row in set (0.00 sec)
备注:两个日期相差17704天.
3.查看两个日期之间相差多少周
mysql> select datediff('2018-06-26','1970-01-05') / 7;+-----------------------------------------+
| datediff('2018-06-26','1970-01-05') / 7 |
+-----------------------------------------+
| 2529.1429 |
+-----------------------------------------+
1 row in set (0.00 sec)
备注:两个日期相差2529.1429周即整数周是2529周.
3.1计算整数值,使用floor函数计算小于等于这个数的最大整数
mysql> select floor(datediff('2018-06-26','1970-01-05') / 7);+------------------------------------------------+
| floor(datediff('2018-06-26','1970-01-05') / 7) |
+------------------------------------------------+
| 2529 |
+------------------------------------------------+
1 row in set (0.00 sec)
4.计算2018年6月26日这个日期的周一是几号
mysql> select adddate('1970-01-05',interval floor(datediff('2018-06-26','1970-01-05') / 7)*7 day);+-------------------------------------------------------------------------------------+
| adddate('1970-01-05',interval floor(datediff('2018-06-26','1970-01-05') / 7)*7 day) |
+-------------------------------------------------------------------------------------+
| 2018-06-25 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
备注:所以,1970年1月5号这个周一加上7*2529天,就是2018年6月26号所在的周一.
5.计算2018年6月25日这个日期的周一是几号的过程
mysql> select datediff('2018-06-25','1970-01-05') / 7;+-----------------------------------------+
| datediff('2018-06-25','1970-01-05') / 7 |
+-----------------------------------------+
| 2529.0000 |
+-----------------------------------------+
1 row in set (0.00sec)
mysql> select floor(datediff('2018-06-25','1970-01-05') / 7);+------------------------------------------------+
| floor(datediff('2018-06-25','1970-01-05') / 7) |
+------------------------------------------------+
| 2529 |
+------------------------------------------------+
1 row in set (0.00sec)
mysql> select adddate('1970-01-05',interval floor(datediff('2018-06-25','1970-01-05') / 7)*7 day);+-------------------------------------------------------------------------------------+
| adddate('1970-01-05',interval floor(datediff('2018-06-25','1970-01-05') / 7)*7 day) |
+-------------------------------------------------------------------------------------+
| 2018-06-25 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
备注:两个日期相差正好是2529周,所以呢,就使用1970年1月5号这个日期加上7*2529天就是周一了.
6.计算2018年6月24日这个日期所在的周一是几号
mysql> select datediff('2018-06-24','1970-01-05') / 7;+-----------------------------------------+
| datediff('2018-06-24','1970-01-05') / 7 |
+-----------------------------------------+
| 2528.8571 |
+-----------------------------------------+
1 row in set (0.00sec)
mysql> select floor(datediff('2018-06-24','1970-01-05') / 7);+------------------------------------------------+
| floor(datediff('2018-06-24','1970-01-05') / 7) |
+------------------------------------------------+
| 2528 |
+------------------------------------------------+
1 row in set (0.00sec)
mysql> select adddate('1970-01-05',interval floor(datediff('2018-06-24','1970-01-05') / 7)*7 day);+-------------------------------------------------------------------------------------+
| adddate('1970-01-05',interval floor(datediff('2018-06-24','1970-01-05') / 7)*7 day) |
+-------------------------------------------------------------------------------------+
| 2018-06-18 |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
小结:
简单来说,就是某个日期和某个周一之间差多少整数周,相加就是某个日期所在的周一了