mybatis+mysql时间范围查询,MySql,mybatis查询当天、本周、本月、本季度、本年的数据

一、前言

很多时间我们需要查询指定时间段内的数据，比如某一个，某一个月，某一年的数据，Mysql中有两个方法，

1.DATE_FORMAT (date, format)能根据格式串format 格式化日期或日期和时间值date，返回结果字符串。
2.STR_TO_DATE(str,format) 将制定日期格式字符串时间表达式转化为 DTAE类型

常用format

format	结果示例
%Y-%m-%d	2021-08-17
%Y-%m-%d %H:%i:%s	2021-08-17 04:55:46
%b %d %Y %h:%i %p	Aug 17 2021 08:21 PM

在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') 不走索引

一般有以下几种方式去查：

方式一、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}

二、常用时间查询SQL语句

今天

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;

近7天

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段名);

本月

SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

6个月

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 DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

SELECT * FROM 表名 WHERE WEEKOFYEAR(FROM_UNIXTIME(时间字段名,'%y-%m-%d')) = WEEKOFYEAR(NOW()); 

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(NOW());

上年

SELECT * FROM 表名 WHERE YEAR(时间字段名) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));