MySQL创建表时统一两种日期格式的技术问询

嘿，我明白你现在的需求——创建tweets_adj表的同时，得把tweet表里同一列存的两种文本格式日期统一处理成标准日期类型对吧？我来给你梳理个可行的方案：

首先，核心思路是用CASE语句结合日期转换函数，先判断每条记录的日期属于哪种格式，再分别转换为统一的日期类型（比如DATETIME）。下面是补全并优化后的SQL代码，我假设你的日期列是t.created_at，同时补全了你没写完的文本处理部分：

CREATE TABLE tweets_adj 
SELECT 
    t.fullname AS company_name,
    t.`user` AS twitter_user_name,
    t.id AS tweet_id,
    CAST(t.likes AS UNSIGNED INT) AS likes,
    CAST(t.replies AS UNSIGNED INT) AS replies,
    CAST(t.retweets AS UNSIGNED INT) AS retweets,
    -- 处理你提到的特殊文本格式
    CASE 
        WHEN RIGHT(t.`text`, 1) = "'" AND LEFT(t.`text`, 2) = "b'" 
        THEN LEFT(REPLACE(t.`text`, "b'", ""), LENGTH(t.`text`) - 2)
        ELSE t.`text`
    END AS tweet_text,
    -- 关键：统一转换两种日期格式
    CASE
        -- 先匹配第一种日期格式（这里假设是yyyy-MM-dd HH:mm:ss，你可以根据实际格式修改）
        WHEN STR_TO_DATE(t.created_at, '%Y-%m-%d %H:%i:%s') IS NOT NULL
        THEN STR_TO_DATE(t.created_at, '%Y-%m-%d %H:%i:%s')
        -- 再匹配第二种日期格式（比如dd/MM/yyyy HH:mm:ss，同样按需调整）
        WHEN STR_TO_DATE(t.created_at, '%d/%m/%Y %H:%i:%s') IS NOT NULL
        THEN STR_TO_DATE(t.created_at, '%d/%m/%Y %H:%i:%s')
        -- 若两种格式都不匹配，可设为NULL或保留原文本，根据你的需求调整
        ELSE NULL
    END AS tweet_date
FROM your_original_tweet_table t; -- 记得把这里替换成你实际的原表名

几个关键说明： #

• 格式符调整：你得先确认实际的两种日期格式是什么，可以先跑这条SQL看样本数据：SELECT DISTINCT created_at FROM your_original_tweet_table LIMIT 10;，然后把STR_TO_DATE里的格式符换成对应格式（比如如果是MM/dd/yyyy就用'%m/%d/%Y'）。
• 数据库适配：如果你用的不是MySQL，比如PostgreSQL，把STR_TO_DATE换成TO_TIMESTAMP或者TO_DATE，格式符语法略有不同（比如PostgreSQL用YYYY-MM-DD HH24:MI:SS）。
• 异常处理：如果存在既不匹配第一种也不匹配第二种格式的日期，上面的代码会返回NULL，你可以把ELSE部分改成t.created_at来保留原文本，或者根据业务需求做其他处理。
• 列类型控制：如果需要更精准地定义tweets_adj表的列类型，你可以先建表再插入数据，比如：

  CREATE TABLE tweets_adj (
      company_name VARCHAR(255),
      twitter_user_name VARCHAR(255),
      tweet_id BIGINT,
      likes INT UNSIGNED,
      replies INT UNSIGNED,
      retweets INT UNSIGNED,
      tweet_text TEXT,
      tweet_date DATETIME
  );
  
  INSERT INTO tweets_adj
  -- 这里放上面的SELECT查询内容
  

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