MySQL系列(6): 日期时间范围查询几种方式
经常需要查询一段日期范围内的数据,可能需要对日期时间进行格式转换以达到真正想要的数据,这里整理了几种方式。
范围条件查询
- 指定开始时间和结束时间,直接使用
>、>=、<、<=
进行查询。 - 也可使用
where .... between 'start' and ''end'
以上两种方式因时间类型不是整数型,在比较时效率较低,但数据量较大时,可将时间转为UNIX时间戳。
转时间戳比较
每个时间,都会有对应的时间戳,可使用MySQL内置的传统时间转时间戳互换函数。
- 传统时间转时间戳:
SELECT UNIX_TIMESTAMP(datetime);
例:SELECT UNIX_TIMESTAMP(‘2010-03-01 00:00:00’); - 时间戳转传统时间:
FROM_UNIXTIME(unixtime)
例:SELECT FROM_UNIXTIME(1505305321);
此方法有利于快速查询时间段,不过显示时间则需要再反转一次。
范围查询包头不包尾问题
如果对 date 型日期范围查询时,结束日期默认是结束日的 00:00:00,结束日期时间大于这个值时就不在查询范围内,如:00:00:01,有以下三种方式解决。
- 使用 DATE_FORMAT() 将 date 型转为字符串,再使用 concat() 函数对日期字符串拼接时分秒。
1
2
3SELECT * FROM order
WHERE '2017-09-30' >= startDate
AND '2017-09-30 00:00:01' <= CONCAT(DATE_FORMAT(endDate,'%Y-%m-%d'),' 23:59:59'); - 将 date 型字段转为时间戳,并加上 23:59:59 的时间秒数,即 86399 秒。
1
2
3SELECT * FROM order
WHERE '2017-09-30' >= startDate
AND UNIX_TIMESTAMP('2017-09-30 00:00:01') <= (UNIX_TIMESTAMP(endDate) + 86399); - 将传入的时分秒日期格式转换为年月日,只对年月日进行范围查询。
1
2
3
4SELECT * FROM order
WHERE DATE_FORMAT('2015-10-01 00:00:01','%Y-%m-%d') >= startDate
AND DATE_FORMAT('2015-10-31 23:59:59','%Y-%m-%d') <= endDate
AND createDateTime = (SELECT MAX(createDateTime) FROM order);
建议使用第三种方式,如果时间字段涉及到更复杂的计算,则用第二种方式。
MySQL系列(6): 日期时间范围查询几种方式
http://blog.gxitsky.com/2019/01/23/MySQL-06-select-datetime-scope/