MongoDB到Redshift数据迁移中Varchar转Datetime格式问题求助

一、格式转换SQL语句 #

针对你的日期列两种格式，直接用Redshift内置的日期转换函数即可完成标准化，无需复杂正则。以下是具体的查询/更新语句：

查询验证语句

SELECT
  date_column AS original_date,
  CASE
    -- 匹配格式1（包含GMT标识）
    WHEN date_column LIKE '%GMT%' THEN
      TO_CHAR(
        TO_TIMESTAMP(date_column, 'Dy Mon DD YYYY HH24:MI:SS "GMT+0000 (Coordinated Universal Time)"'),
        'YYYY-MM-DD HH24:MI:SS.FF3'
      )
    -- 格式2直接标准化（确保格式统一，避免原有格式可能的不规范）
    ELSE TO_CHAR(TO_TIMESTAMP(date_column, 'YYYY-MM-DD HH24:MI:SS.FF3'), 'YYYY-MM-DD HH24:MI:SS.FF3')
  END AS standardized_date
FROM your_target_table;

批量更新语句

如果需要直接修正表中的数据：

UPDATE your_target_table
SET date_column = 
  CASE
    WHEN date_column LIKE '%GMT%' THEN
      TO_CHAR(
        TO_TIMESTAMP(date_column, 'Dy Mon DD YYYY HH24:MI:SS "GMT+0000 (Coordinated Universal Time)"'),
        'YYYY-MM-DD HH24:MI:SS.FF3'
      )
    ELSE TO_CHAR(TO_TIMESTAMP(date_column, 'YYYY-MM-DD HH24:MI:SS.FF3'), 'YYYY-MM-DD HH24:MI:SS.FF3')
  END;

二、关键函数说明 #

• TO_TIMESTAMP(string, format)：将指定格式的字符串转换为Redshift的timestamp类型，格式模板中的双引号用于匹配固定文本（比如格式1末尾的GMT+0000 (Coordinated Universal Time)是固定内容）。
• TO_CHAR(timestamp, format)：将timestamp类型转换为指定格式的字符串，FF3表示保留3位毫秒数，和格式2完全匹配。

三、后续数据统一方案 #

为避免后续再出现格式混乱，建议从源头解决：

• ETL/导出阶段：从MongoDB抽取数据时，直接将日期字段转换为YYYY-MM-DD HH24:MI:SS.sss格式的字符串后再写入Redshift。
• Redshift表结构优化：将目标列的类型从varchar改为timestamp，Redshift会自动识别合法的日期字符串并存储为标准timestamp类型，查询时可按需转换为字符串格式，从根本上避免格式问题。

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