MySQL字符串列日期转换求助：GMT转带时区标准格式

问题分析 #

你之前使用的STR_TO_DATE('Wed Jun 01 2022', '%Y %M %D')格式符完全不匹配原始字符串结构，导致解析失败。原始字符串Wed Jun 01 2022 05:04:03 GMT的结构是缩写星期名+缩写月份名+日+年+时分秒，需要对应正确的格式符。

分步解决 #

1. 解析原始日期字符串
   使用STR_TO_DATE配合正确的格式符解析GMT时间：

   STR_TO_DATE('Wed Jun 01 2022 05:04:03 GMT', '%a %b %d %Y %H:%i:%s')
   

   格式符说明：

   • %a: 缩写星期名（如Wed）
   • %b: 缩写月份名（如Jun）
   • %d: 两位数字的日期（如01）
   • %Y: 四位数字的年份（如2022）
   • %H:%i:%s: 24小时制的时分秒（如05:04:03）

2. 转换时区并格式化目标格式
   原始时间为GMT（UTC+0），需转换为UTC-5时区的ISO 8601格式，使用CONVERT_TZ转换时区后，用DATE_FORMAT输出目标格式：

   SELECT DATE_FORMAT(
     CONVERT_TZ(
       STR_TO_DATE('Wed Jun 01 2022 05:04:03 GMT', '%a %b %d %Y %H:%i:%s'),
       '+00:00',  -- 原始时区（GMT/UTC）
       '-05:00'   -- 目标时区偏移
     ),
     '%Y-%m-%dT%H:%i:%s-05:00'
   );
   

   执行后将输出：2022-06-01T00:04:03-05:00（注：你示例中的秒数为29属于输入误差，实际转换后秒数与原始时间一致）

补充说明 #

如果你的BI工具支持日期类型的可视化配置，也可以先通过SQL解析为日期时间类型，再在BI工具中设置时区和显示格式，无需在SQL层拼接格式字符串。

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