MySQL同表datetime按时区字段转换CONVERT_TZ返回NULL如何解决

问题场景 #

• 营业时间表openingtimes核心字段：
  • openingtime：DATETIME类型，存储服务器所在时区（Europe/Paris巴黎时区）的时间值
  • timezone：存储营业点对应的IANA标准时区标识，例如Europe/London
• 需求：查询时将openingtime从服务器时区转换为对应营业点的本地时间输出
• 示例数据：

2022-07-08 11:00:00 | Europe/London

• 预期输出：2022年7月巴黎处于夏令时（UTC+2），伦敦处于夏令时（UTC+1），比巴黎慢1小时，正确结果为2022-07-08 10:00:00
• 执行以下查询时所有结果均返回NULL：

SELECT CONVERT_TZ(openingtime, 'Europe/Paris', timezone) 
FROM openingtimes

故障原因 #

CONVERT_TZ()返回NULL的核心原因只有两类：

1. 传入的时间值格式非法
2. 传入的命名时区（如Europe/Paris这类字符串格式时区标识）无法被MySQL识别，这是99%场景下的故障原因：MySQL默认不会自动加载系统的IANA时区数据库到内置系统表中，未手动导入时区表时，所有命名时区都无法被函数识别，直接返回NULL。

修复方案 #

方案1：导入时区表（推荐，支持夏令时自动计算，一劳永逸） #

在服务器命令行执行以下命令，将系统自带的IANA时区数据导入到MySQL的mysql系统库中：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

执行时输入MySQL root账号密码，导入完成后重启MySQL服务即可。

Windows环境无自带mysql_tzinfo_to_sql工具，可手动下载对应MySQL版本的预编译时区表包，解压后导入mysql库即可。

导入完成后先执行验证语句确认配置生效：

SELECT CONVERT_TZ('2022-07-08 11:00:00', 'Europe/Paris', 'Europe/London');

返回结果为2022-07-08 10:00:00即代表配置正常，原业务查询可直接返回正确结果。
如果导入后仍返回NULL，先检查timezone字段值是否存在拼写错误、多余空格，必须和IANA时区标识完全一致才能被正常识别。

方案2：使用UTC偏移量临时替换（不推荐） #

如果暂时无法修改服务器配置导入时区表，可直接传入时区对应的UTC偏移量作为参数，但该方式无法自动处理夏令时切换，时间计算存在误差，仅适合临时排查使用：

-- 示例为夏令时场景下的偏移量，冬令时需要手动调整偏移值
SELECT CONVERT_TZ(openingtime, '+02:00', '+01:00') 
FROM openingtimes

该方案无法适配timezone字段动态存储不同时区的业务场景，不建议生产环境使用。

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