如何在MySQL中将带时区的字符串转换为UTC日期及UNIX时间戳？

问题拆解 #

你用STR_TO_DATE的时候踩了两个坑：一是格式符写错了（原字符串日期和月份之间是空格，不是横杠），二是这个函数确实不处理时区，会直接忽略EST这类标识，导致转换结果用的是当前会话时区，肯定不准。而CONVERT_TZ的难点在于怎么从字符串里提取有效时区参数。

具体解决步骤 #

1. 拆分日期与时区，分步转换 #

先把字符串里的日期主体和时区拆分开，再组合转换：

• 先拆分出日期部分和时区部分：

  SELECT 
    SUBSTRING_INDEX("Tue, 15 May 2012 17:26:44 EST", ' ', 5) AS date_part,
    SUBSTRING_INDEX("Tue, 15 May 2012 17:26:44 EST", ' ', -1) AS tz_part;
  

• 接着把日期转成datetime，再用CONVERT_TZ转UTC，最后转成UNIX时间戳：

  SELECT 
    UNIX_TIMESTAMP(
      CONVERT_TZ(
        STR_TO_DATE(SUBSTRING_INDEX("Tue, 15 May 2012 17:26:44 EST", ' ', 5), "%a, %d %b %Y %T"),
        SUBSTRING_INDEX("Tue, 15 May 2012 17:26:44 EST", ' ', -1),
        'UTC'
      )
    ) AS utc_unix_ts;
  

2. 处理时区缩写不识别的问题 #

MySQL对时区缩写的支持有限，比如EST可能对应不同时区，遇到不识别的缩写时CONVERT_TZ会返回NULL。建议你维护一个自定义的时区映射表，把业务中出现的缩写对应到标准时区格式（比如EST对应America/New_York）：

-- 先创建映射表（示例）
CREATE TABLE timezone_map (
  abbr VARCHAR(10) PRIMARY KEY,
  tz VARCHAR(50) NOT NULL
);
INSERT INTO timezone_map VALUES ('EST', 'America/New_York'), ('PST', 'America/Los_Angeles');

-- 关联查询转换
SELECT 
  UNIX_TIMESTAMP(
    CONVERT_TZ(
      STR_TO_DATE(SUBSTRING_INDEX(t.your_date_col, ' ', 5), "%a, %d %b %Y %T"),
      m.tz,
      'UTC'
    )
  ) AS utc_unix_ts
FROM your_table t
JOIN timezone_map m ON SUBSTRING_INDEX(t.your_date_col, ' ', -1) = m.abbr;

3. 不推荐的临时方案 #

如果临时测试用，可以先设置会话时区为目标时区再转换，但因为你记录里时区不固定，这个方法不适合批量处理：

SET time_zone = 'EST';
SELECT UNIX_TIMESTAMP(CONVERT_TZ(STR_TO_DATE("Tue, 15 May 2012 17:26:44 EST", "%a, %d %b %Y %T"), @@session.time_zone, 'UTC'));

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