MySQL系列(6): 日期时间范围查询几种方式

  经常需要查询一段日期范围内的数据,可能需要对日期时间进行格式转换以达到真正想要的数据,这里整理了几种方式。

范围条件查询

  1. 指定开始时间和结束时间,直接使用>、>=、<、<=进行查询。
  2. 也可使用 where .... between 'start' and ''end'

以上两种方式因时间类型不是整数型,在比较时效率较低,但数据量较大时,可将时间转为UNIX时间戳。

转时间戳比较

每个时间,都会有对应的时间戳,可使用MySQL内置的传统时间转时间戳互换函数。

  1. 传统时间转时间戳:SELECT UNIX_TIMESTAMP(datetime);
    例:SELECT UNIX_TIMESTAMP(‘2010-03-01 00:00:00’);
  2. 时间戳转传统时间:FROM_UNIXTIME(unixtime)
    例:SELECT FROM_UNIXTIME(1505305321);

此方法有利于快速查询时间段,不过显示时间则需要再反转一次。

范围查询包头不包尾问题

如果对 date 型日期范围查询时,结束日期默认是结束日的 00:00:00,结束日期时间大于这个值时就不在查询范围内,如:00:00:01,有以下三种方式解决。

  1. 使用 DATE_FORMAT()date 型转为字符串,再使用 concat() 函数对日期字符串拼接时分秒。
    1
    2
    3
    SELECT * FROM order 
    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 秒。
    1
    2
    3
    SELECT * FROM order 
    WHERE '2017-09-30' >= startDate
    AND UNIX_TIMESTAMP('2017-09-30 00:00:01') <= (UNIX_TIMESTAMP(endDate) + 86399);
  3. 将传入的时分秒日期格式转换为年月日,只对年月日进行范围查询。
    1
    2
    3
    4
    SELECT * 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/

作者

光星

发布于

2019-01-23

更新于

2023-03-06

许可协议

评论