MySQL查询中日期字段与日期字符串比较不生效问题排查

日期匹配失效、DATEDIFF计算结果偏差1天的核心原因是 MySQL时区配置不一致，你写的SQL语法本身没有错误。
从范围查询返回的结果可以判断：存储在schedule_date字段里的时间值，和当前会话解析传入日期字符串时使用的时区存在偏移，偏移量刚好跨过了日期分界点，就会出现对应的异常现象：

• 传入'2022-06-04'做匹配时，字段实际解析出的日期比预期多1天，DATEDIFF计算结果为1
• 不管是直接等值比较、还是用DATE()函数转换字段/值做比较，都会因为时区偏移导致匹配不到预期记录

排查步骤 #

先执行以下SQL确认时区配置：

-- 查看当前连接会话的时区
SELECT @@session.time_zone;
-- 查看数据库全局配置时区
SELECT @@global.time_zone;

如果返回值为SYSTEM，再确认数据库部署服务器的系统时区。最常见的异常场景是数据库服务端配置为UTC时区，客户端连接时默认使用东八区时区，两者存在8小时时差，刚好碰到时间在日期临界点附近的记录时，就会出现日期差1天的问题。

修复方案 #

根据实际场景选以下任意一种方案即可：

• 连接数据库时主动指定正确时区：比如JDBC连接串追加参数serverTimezone=Asia/Shanghai，命令行连接时增加参数--default-time-zone='+08:00'，保证客户端连接时区和业务使用时区一致。
• 统一数据库全局时区为业务使用时区：执行SQL修改全局配置

  SET GLOBAL time_zone = '+08:00';
  

  修改后需要重新建立连接才会生效，建议同步修改MySQL配置文件my.cnf中的default-time-zone参数，避免服务重启后配置失效。
• 临时不改配置的场景下，可以在SQL中手动做时区转换后再匹配：

  SELECT s.schedule_date 
  FROM schedule s 
  WHERE DATE(CONVERT_TZ(s.schedule_date, '+00:00', '+08:00')) = '2022-06-04'
  

  语句中的时区偏移值替换为实际的存储时区和业务时区差值即可。

验证方法 #

时区配置统一后，重新执行之前的范围查询SQL，DATEDIFF(s.schedule_date,"2022-06-04")的返回值会变为0，三种日期等值匹配写法也都能正常返回预期结果。

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