MySQL查询指定日期条件却返回前一天记录问题排查求助

问题根因 #

这一问题的核心原因是生产库与测试库的时区配置不一致，具体逻辑如下：
你返回结果中的transaction_date后缀带Z，代表该时间为UTC（世界标准时间），UTC时间2021-11-18T18:30:00.000Z换算为东八区（北京时间）刚好是2021-11-19 02:30:00。
你查询时输入的日期2021-11-19是东八区日期，但生产库MySQL服务端时区被设置为UTC，执行DATE(transaction_date)转换时使用UTC时区计算，得到的日期是2021-11-18，和你输入的东八区日期不匹配，才会出现返回前一天记录的问题。测试库时区和你本地时区一致，因此查询正常。
你提到transaction_date为DATE类型，但返回结果带时分秒，大概率是字段实际类型为DATETIME/TIMESTAMP，或是应用层序列化时额外加了时间信息，不影响时区问题的定位。

排查验证步骤 #

执行以下SQL确认生产库时区配置：

SHOW VARIABLES LIKE '%time_zone%';

如果返回的system_time_zone是UTC，time_zone是SYSTEM，即可确认是时区问题。

解决思路 #

方案1：统一服务端时区（推荐） #

修改生产库MySQL配置文件my.cnf（Windows系统为my.ini），添加以下配置：

default-time-zone = '+8:00'

修改完成后重启MySQL服务即可永久生效。如果需要临时生效无需重启，可执行：

SET GLOBAL time_zone = '+8:00';
SET time_zone = '+8:00';

该方案可以保证生产、测试库行为一致，后续所有日期查询无需额外改造。

方案2：查询时显式指定时区（无需改服务端配置） #

如果无法修改生产库配置，可在查询时用CONVERT_TZ函数显式转换时区：

SELECT 
    transaction_date, 
    DATE(CONVERT_TZ(transaction_date, '+00:00', '+8:00')) AS local_date
FROM `table`
WHERE DATE(CONVERT_TZ(transaction_date, '+00:00', '+8:00')) = '2021-11-19'

方案3：按UTC时间范围查询（性能最优） #

如果确认所有时间都是按UTC存储，可将查询日期转换为UTC时间范围，该写法可以用到transaction_date的索引，性能比前两种更高：

SELECT 
    transaction_date, 
    DATE(transaction_date)
FROM `table`
WHERE transaction_date >= '2021-11-18 16:00:00' 
AND transaction_date < '2021-11-19 16:00:00'

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