mybatis+mysql指定范围进行时间检索
1.前言
在我们的业务中，有时候会根据时间进行检索，例如查找一周内，一月内的数据，在这种业务需求中，我们会使用如下两个方法进行数据的检索：
方法一：
| DATE_FORMAT (date,format) |
根据format格式化date,返回结果为字符串
方法二：
将指定日期格式字符串时间表达式转换为DARE类型
常用的format格式
| format |
结果展示 |
| %Y-%m-%d |
2022-08-02 |
| %Y-%m-%d %H:%i:%s |
2022-08-02 08:54:05 |
| %b %d %Y %h:%i %p |
Aug 02 2022 08:54 AM |
在mysql中字符串日期可以直接和datetime类型之间比较，无需转换，Mysql会将字符串类型日期转换成长整型数字进行比较，当然你也可以转换为同一类型后再比较。
mysql日期与字符串转换函数：
| 字符串转日期：STR_TO_DATE('2021-08-17 00:00:33', '%Y-%m-%d %H:%i:%s') 走索引 |
| 日期转字符串：DATE_FORMAT('2021-08-17 00:00:33', '%Y-%m-%d %H:%i:%s') 不走索引 |
2.上干货
方式一：between·…and
| SELECT * FROM k_student WHERE create_time between '2021-08-17 00:00:33' and '2021-08-17 00:54:33' |
方式二: 大小于号
| SELECT * FROM k_student WHERE create_time >= '2021-08-17 00:00:33' AND create_time <= '2019-07-25 00:54:32' |
方式三: 转换为UNIX_TIMESTAMP比较，create_time若加了索引，不走索引
| SELECT * FROM k_student WHERE UNIX_TIMESTAMP(create_time) between UNIX_TIMESTAMP('2021-08-17 00:00:33') and UNIX_TIMESTAMP('2021-08-17 00:54:33') |
对应在mybatis中的就是：
| select * from table_name where user_id=#{userId} AND DATE_FORMAT(order_time,'%Y-%m-%d') between #{start} AND #{end} |
3.常用查询时间
| 当天： SELECT * FROM 表名 WHERE TO_DAYS(时间字段名) = TO_DAYS(NOW()); |
| 昨天： SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1; |
| 本周： SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW()); |
| 上周： SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW())-1; |
| 近七天： SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段名); |
| 本月： SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m'); |
| 近六个月： SELECT * FROM 表名 WHERE 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW(); |
| 上月： |
| SELECT * FROM 表名 WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(时间字段名,'%Y%m')) = 1; |
| SELECT * FROM 表名 WHERE WEEKOFYEAR(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = WEEKOFYEAR(NOW()); |
| SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE() |
| SELECT * FROM 表名 WHERE MONTH(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = MONTH(NOW()); |
| SELECT * FROM 表名 WHERE YEAR(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = YEAR(NOW()) AND MONTH(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = MONTH(NOW()); |
| 本季度：SELECT * FROM 表名 WHERE QUARTER(时间字段名) = QUARTER(NOW()); |
| 上个季度： SELECT * FROM 表名 WHERE QUARTER(时间字段名) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)); |
| 当年： SELECT * FROM 表名 WHERE YEAR(时间字段名)=YEAR(NOW()); |
| 上年：SELECT * FROM 表名 WHERE YEAR(时间字段名) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)); |