MyBatis日期时间的范围查询

　　最近做报表统计，对日期时间范围查找的比较多，经常会遇到，也有些之前涉及相关的零散笔记，现整理如下。

　　MyBatis 在接收日期时间参数进行日期时间范围查询时，有些需要注意的地方，例如日期时间参数的数据类型可能是Date或String类型，或传参与数据库存储的日期时间格式并不一致等情况。

　　此情况更多与 SQL 的字符串拼接或日期时间格式转换有关。

传参：字符串类型

数据库字段类型是 datetime 或 varchar。

1. 传入起始时间(beginDate)和结束时间(beginDate)两个参数，在SQL可以使用between and或 > = <，在数据类型上可以不用转换处理。
   between...and方式

   <!-- between and -->
   <select id="queryByTime" resultType="user">
       select * from user where apply_time between #{beginDate} and #{endDate}
   </select>
   
   解析后执行的SQL: 
   select * from user where create_time between '2018-07-17 17:26:36' and '2018-07-18 17:50:55'
   

   大于、等于、小于方式：使用> = <符号，需要使用 <![CDATA[ 文本 ]] 将内容包围以告诉XML解析时不对文本进行解析

   <select id="queryByTime" resultType="user">
       select * from user where 
       <![CDATA[ apply_time >= #{beginDate} and apply_time <= #{endDate} ]]>
   </select>
   
   解析后执行的SQL: 
   select * from user where create_time >= '2018-07-17 17:26:36' and create_time <= '2018-07-18 17:50:55'
   

2. 只传入一个日期参数(queryDate)，要查一天的数据，就需要拼接时间字符串了。

   <select id="queryByTime" resultType="user">
       <bind name="startTime" value="queryDate+' 00:00:00'"/>
       <bind name="endTime" value="queryDate+' 23:59:59'"/>
       select * from user where apply_time between #{startTime} and #{endTime}
   </select>
   
   解析后执行的SQL:
   select * from user where apply_time between '2018-07-17 00:00:00' and '2018-07-17 23:59:59'
   

备注：MyBatis 的 SQL 传参是字符串类型时，参数格式可以是yyyy-MM-dd,相当于yyyy-MM-dd 00:00:00, 当用作结束时间时要注意拼接 "23:59:59" ,否则会出现查询范围包头不包尾的情况。

传参：日期时间类型

1. 使用 DATE_FORMAT() 将 date 型转为字符串，再使用concat()函数对日期字符串拼接时分秒。

    SELECT * FROM user WHERE '2017-09-30' >= startDate AND '2017-09-30 00:00:01' <= CONCAT(DATE_FORMAT(endDate,'%Y-%m-%d'),' 23:59:59');
   

2. 将 date 型字段转为时间戳，并加上 23:59:59 的时间秒数，即 86399 秒。

    SELECT * FROM user WHERE '2017-09-30' >= startDate AND UNIX_TIMESTAMP('2017-09-30 00:00:01') <= (UNIX_TIMESTAMP(endDate) + 86399);
   

3. 将传入的时分秒日期格式转换为年月日，只对年月日进行范围查询。

    SELECT * FROM user WHERE DATE_FORMAT('2017-09-30 00:00:01','%Y-%m-%d') >= startDate AND DATE_FORMAT('2017-09-30 23:59:59','%Y-%m-%d') <= endDate AND createDateTime = (SELECT MAX(createDateTime) FROM user);
   

建议使用第三种方式，如果时间字段涉及到更复杂的计算，则用第二种方式。