如何让MySQL SELECT返回带时区缩写的datetime结果？

需求说明 #

需要从datetime类型列中查询出包含时区缩写（如2023-07-17 12:34:56 PDT）的结果，且要求跨时区查询时能自动正确计算夏令时切换。DATE_FORMAT函数本身不支持直接输出时区缩写，可通过以下方案实现。

解决方案 #

MySQL没有直接返回时区缩写的内置函数，但可以结合CONVERT_TZ（处理时区转换及夏令时）和逻辑判断来拼接时区缩写，以下提供两种可行方法：

方法一：利用TIME_ZONE_OFFSET判断偏移量（MySQL 8.0+适用）

TIME_ZONE_OFFSET函数可根据指定时区和时间，返回对应的UTC偏移量，我们可以通过偏移量映射到对应的时区缩写：

SELECT 
  CONCAT(
    DATE_FORMAT(conv_tz, '%Y-%m-%d %H:%i:%s'),
    ' ',
    CASE TIME_ZONE_OFFSET('America/Los_Angeles', conv_tz)
      WHEN '-08:00' THEN 'PST'
      WHEN '-07:00' THEN 'PDT'
    END
  ) AS formatted_time
FROM (
  -- 先将UTC时间转换为目标时区时间
  SELECT CONVERT_TZ(`timestamp`, 'UTC', 'America/Los_Angeles') AS conv_tz
  FROM deleteme
) t;

执行后将得到预期结果：

+---------------------------+
| formatted_time            |
+---------------------------+
| 2023-03-01 04:34:56 PST   |
| 2023-04-01 05:34:56 PDT   |
+---------------------------+

方法二：通过日期范围判断夏令时（兼容MySQL 5.7及以下）

如果使用较低版本MySQL，可通过判断时间是否在目标时区的夏令时区间内，手动拼接缩写。以America/Los_Angeles时区为例，夏令时为每年3月第二个周日2点至11月第一个周日2点：

SELECT 
  CONCAT(
    DATE_FORMAT(CONVERT_TZ(`timestamp`, 'UTC', 'America/Los_Angeles'), '%Y-%m-%d %H:%i:%s'),
    ' ',
    CASE 
      WHEN CONVERT_TZ(`timestamp`, 'UTC', 'America/Los_Angeles') BETWEEN 
           -- 计算当年3月第二个周日2点
           STR_TO_DATE(CONCAT(YEAR(`timestamp`), '-03-01'), '%Y-%m-%d') + INTERVAL (13 - DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(`timestamp`), '-03-01'), '%Y-%m-%d'))) DAY + INTERVAL 2 HOUR
           AND 
           -- 计算当年11月第一个周日2点
           STR_TO_DATE(CONCAT(YEAR(`timestamp`), '-11-01'), '%Y-%m-%d') + INTERVAL (1 - DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(`timestamp`), '-11-01'), '%Y-%m-%d'))) DAY + INTERVAL 2 HOUR
      THEN 'PDT'
      ELSE 'PST'
    END
  ) AS formatted_time
FROM deleteme;

注意事项 #

• 确保MySQL已加载时区表：可通过SELECT * FROM mysql.time_zone LIMIT 1;验证，若无结果需导入时区数据（可参考官方文档对应系统的导入方式）。
• 不同时区的夏令时规则不同，需根据目标时区调整判断逻辑或偏移量映射关系。

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