MySQL中如何查询varchar类型存储的AM/PM 12小时制时间区间数据

问题原因 #

直接对12小时制的varchar时间做字符串比较是错误的，MySQL只会按字符的ASCII码顺序判断，不会识别am/pm的时间语义。比如11:20:23 pm字符串开头的11:和11:13:00 am开头的11:字符一致，会被判定为落在11:13:00 am到11:59:59 am的字符串区间内，所以返回了不符合要求的pm数据。 #

单天指定时间范围查询写法 #

使用STR_TO_DATE函数将varchar类型的日期、时间转为MySQL内置的日期时间类型，再做范围判断即可：

SELECT * FROM `table` 
WHERE 
  -- 注意：如果你的date字段是「月-日-年」格式，把下面的'%d-%m-%Y'改成'%m-%d-%Y'
  STR_TO_DATE(`date`, '%d-%m-%Y') = '2021-09-10'
  -- 转12小时制时间为TIME类型后比较
  AND STR_TO_DATE(`time`, '%h:%i:%s %p') BETWEEN '11:13:00' AND '12:13:59';

转换为TIME类型后，11:20:23 pm会被识别为23:20:23，自然不在要求的时间范围内，不会被误返回。 #

1小时时间范围通用查询方法 #

场景1：单天内指定起始时间查往后1小时 #

SELECT * FROM `table`
WHERE
  STR_TO_DATE(`date`, '%d-%m-%Y') = '2021-09-10'
  AND STR_TO_DATE(`time`, '%h:%i:%s %p') >= STR_TO_DATE('11:13:00 am', '%h:%i:%s %p')
  AND STR_TO_DATE(`time`, '%h:%i:%s %p') < DATE_ADD(STR_TO_DATE('11:13:00 am', '%h:%i:%s %p'), INTERVAL 1 HOUR);

场景2：跨天的1小时范围查询（比如23:30到次日00:30） #

将日期和时间合并为DATETIME类型后判断：

SELECT * FROM `table`
WHERE
  STR_TO_DATE(CONCAT(`date`, ' ', `time`), '%d-%m-%Y %h:%i:%s %p') 
  BETWEEN '2021-09-10 23:30:00' AND '2021-09-11 00:30:00';

场景3：查询当前时间往前1小时的所有数据 #

SELECT * FROM `table`
WHERE
  STR_TO_DATE(CONCAT(`date`, ' ', `time`), '%d-%m-%Y %h:%i:%s %p') >= DATE_ADD(NOW(), INTERVAL -1 HOUR);

优化建议 #

如果表数据量较大，每次查询对字段做函数转换会无法命中索引，查询效率低。建议后续调整表结构，用DATE类型存储日期，TIME或DATETIME类型存储时间，从根源上避免这类字符串比较的问题，同时提升查询性能。 #

内容的提问来源于stack exchange，提问作者field