相同查询与数据在Dev/Staging库返回不同结果，疑与时区有关？

差异的核心是MySQL的DATETIME类型不存储时区信息，且两个数据库实例依赖系统时区处理日期函数，导致相同的字面时间在不同时区环境下被解析成了不同的实际时间：

1. 字段存储本质：
   • loginTime存储的是山地时间（MDT/MST）的字面DATETIME值（比如2024-05-20 12:00:00），无时区标记。
   • p.created_at存储的是UTC时间的字面DATETIME值，同样无时区标记。
2. 第二个查询的时区冲突：
   • 在Dev库（山地时区）：DATE_SUB(s.loginTime, INTERVAL 6 HOUR)将山地时间字面量减6小时，得到对应的UTC时间（MDT比UTC晚6小时），和p.created_at（UTC字面量）的比较逻辑正确。
   • 在Staging库（UTC时区）：数据库把loginTime的山地时间字面量直接当作UTC时间处理，执行DATE_SUB后得到UTC时间减6小时（和实际需要的UTC时间偏差12小时），和p.created_at的比较逻辑完全错误，导致结果不一致。
3. 第一个查询结果一致的巧合：
   第一个查询用NOW()和转换后的时间比较，NOW()返回数据库服务器当前时区的时间，两边的时区差异被DATE_SUB和NOW()的联动抵消，结果碰巧一致，但逻辑不可靠。

使用MySQL的CONVERT_TZ函数明确指定时区转换，让查询逻辑不依赖数据库服务器的系统时区：

方案1：将loginTime转换为UTC后和p.created_at比较 #

SELECT COUNT(s.id)
FROM app_session_history s
JOIN app_project_usage_history p
ON s.user_id = p.user_id
WHERE CONVERT_TZ(s.loginTime, 'America/Denver', 'UTC') < p.created_at;

（注：America/Denver是山地时间的时区标识符，若MySQL未加载时区表，可使用偏移量'-06:00'/'-07:00'对应MDT/MST，推荐优先使用时区标识符）

方案2：将p.created_at转换为山地时间后和loginTime比较 #

SELECT COUNT(s.id)
FROM app_session_history s
JOIN app_project_usage_history p
ON s.user_id = p.user_id
WHERE s.loginTime < CONVERT_TZ(p.created_at, 'UTC', 'America/Denver');

额外建议 #

• 后续尽量统一数据库的时区配置，或将所有时间字段改为TIMESTAMP类型（TIMESTAMP自动转为UTC存储，查询时转换为会话时区），从根源避免时区问题。
• 确认MySQL时区表完整性：执行SELECT * FROM mysql.time_zone_name;，若为空需导入时区数据。

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