MySQL单列含两种日期格式的转换及新表创建技术问询

针对你遇到的tweets表中timestamp列存在两种时间格式的问题，我来完善并优化SQL方案，帮你顺利生成带标准化日期的新表。

首先，你的思路是完全正确的——用CASE WHEN结合STR_TO_DATE分别匹配两种格式，下面是完整的实现语句：

CREATE TABLE formatted_tweets AS
SELECT
  -- 替换成你需要保留的原表其他字段，比如id、content等
  id,
  content,
  -- 核心逻辑：将两种格式统一转换为DATETIME类型
  CASE
    -- 先尝试解析第一种格式（YYYY-MM-DD HH:MM:SS）
    WHEN STR_TO_DATE(t.timestamp, '%Y-%m-%d %H:%i:%s') IS NOT NULL
      THEN STR_TO_DATE(t.timestamp, '%Y-%m-%d %H:%i:%s')
    -- 第一种解析失败时，尝试第二种格式（DD.MM.YYYY HH:MM）
    ELSE STR_TO_DATE(t.timestamp, '%d.%m.%Y %H:%i')
  END AS formatted_timestamp
FROM tweets t;

补充说明与优化建议： #

• 这个语句会直接创建名为formatted_tweets的新表，转换后的formatted_timestamp是MySQL原生的DATETIME类型，后续做时间范围查询、排序都会更高效。
• 如果担心存在两种格式之外的异常时间值，可以再加一个判断分支，避免转换失败返回NULL（或者设置你需要的默认值）：

  CASE
    WHEN STR_TO_DATE(t.timestamp, '%Y-%m-%d %H:%i:%s') IS NOT NULL
      THEN STR_TO_DATE(t.timestamp, '%Y-%m-%d %H:%i:%s')
    WHEN STR_TO_DATE(t.timestamp, '%d.%m.%Y %H:%i') IS NOT NULL
      THEN STR_TO_DATE(t.timestamp, '%d.%m.%Y %H:%i')
    ELSE NULL -- 这里可以换成你需要的默认时间，比如'1970-01-01 00:00:00'
  END AS formatted_timestamp
  

• 建议先拿小批量数据测试转换结果是否正确，避免直接全量执行出问题：

  SELECT
    timestamp AS original_timestamp,
    CASE
      WHEN STR_TO_DATE(timestamp, '%Y-%m-%d %H:%i:%s') IS NOT NULL
        THEN STR_TO_DATE(timestamp, '%Y-%m-%d %H:%i:%s')
      ELSE STR_TO_DATE(timestamp, '%d.%m.%Y %H:%i')
    END AS formatted_timestamp
  FROM tweets
  LIMIT 100;
  

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