如何批量修正表中格式异常的Timestamp时间戳？

针对Timestamp字段的格式异常问题——小时固定为12，原分钟对应正确时间的小时，原秒对应正确时间的分钟，可通过数据库内置日期处理函数批量转换，以下是主流数据库的实现方案：

Oracle 解决方案 #

Oracle的Timestamp格式与示例匹配，可拆分原字段的日期、分钟、秒和AM/PM标识，重新拼接成正确的Timestamp：

先验证转换结果（测试无误再更新） #

SELECT 
    wrong_ts AS 原异常时间,
    TO_TIMESTAMP(
        TO_CHAR(wrong_ts, 'DD-MON-RR') || ' ' ||
        TO_CHAR(wrong_ts, 'MI') || '.' ||
        TO_CHAR(wrong_ts, 'SS') || '.000000000 ' ||
        TO_CHAR(wrong_ts, 'AM'),
        'DD-MON-RR HH.MI.SS.FF9 AM'
    ) AS 转换后正确时间
FROM your_table
WHERE ROWNUM <= 10; -- 先查看前10条数据的转换效果

批量更新语句 #

验证结果正确后，执行批量更新：

UPDATE your_table
SET correct_ts = TO_TIMESTAMP(
    TO_CHAR(wrong_ts, 'DD-MON-RR') || ' ' ||
    TO_CHAR(wrong_ts, 'MI') || '.' ||
    TO_CHAR(wrong_ts, 'SS') || '.000000000 ' ||
    TO_CHAR(wrong_ts, 'AM'),
    'DD-MON-RR HH.MI.SS.FF9 AM'
);
COMMIT; -- 提交更改

说明：如果不需要保留纳秒部分，可简化格式为'DD-MON-RR HH:MI:SS AM'，拼接字符串时用:分隔时分。

MySQL 解决方案 #

如果原字段是Timestamp类型，可直接提取日期、分钟、秒组件重新组合：

验证转换结果 #

SELECT 
    wrong_ts AS 原异常时间,
    TIMESTAMP(
        DATE(wrong_ts),
        MAKETIME(
            EXTRACT(MINUTE FROM wrong_ts),
            EXTRACT(SECOND FROM wrong_ts),
            0
        )
    ) AS 转换后正确时间
FROM your_table
LIMIT 10;

批量更新语句 #

UPDATE your_table
SET correct_ts = TIMESTAMP(
    DATE(wrong_ts),
    MAKETIME(
        EXTRACT(MINUTE FROM wrong_ts),
        EXTRACT(SECOND FROM wrong_ts),
        0
    )
);
COMMIT;

如果原字段是字符串类型，需先转成日期再处理：

UPDATE your_table
SET correct_ts = STR_TO_DATE(
    CONCAT(
        DATE_FORMAT(STR_TO_DATE(wrong_ts_str, '%d-%b-%y %h.%i.%s.%f %p'), '%d-%b-%y'),
        ' ',
        DATE_FORMAT(STR_TO_DATE(wrong_ts_str, '%d-%b-%y %h.%i.%s.%f %p'), '%i'),
        ':',
        DATE_FORMAT(STR_TO_DATE(wrong_ts_str, '%d-%b-%y %h.%i.%s.%f %p'), '%s'),
        ' 00.000000 ',
        DATE_FORMAT(STR_TO_DATE(wrong_ts_str, '%d-%b-%y %h.%i.%s.%f %p'), '%p')
    ),
    '%d-%b-%y %H:%i:%s.%f %p'
);
COMMIT;

PostgreSQL 解决方案 #

利用to_char拆分原字段组件，再用to_timestamp转换为正确时间：

验证转换结果 #

SELECT 
    wrong_ts AS 原异常时间,
    to_timestamp(
        to_char(wrong_ts, 'DD-Mon-YY') || ' ' ||
        to_char(wrong_ts, 'MI') || ':' ||
        to_char(wrong_ts, 'SS') || ' ' ||
        to_char(wrong_ts, 'AM'),
        'DD-Mon-YY HH12:MI:SS AM'
    ) AS 转换后正确时间
FROM your_table
LIMIT 10;

批量更新语句 #

UPDATE your_table
SET correct_ts = to_timestamp(
    to_char(wrong_ts, 'DD-Mon-YY') || ' ' ||
    to_char(wrong_ts, 'MI') || ':' ||
    to_char(wrong_ts, 'SS') || ' ' ||
    to_char(wrong_ts, 'AM'),
    'DD-Mon-YY HH12:MI:SS AM'
);
COMMIT;

注意事项 #

• 先备份数据：执行更新前务必备份表数据，避免操作失误导致数据丢失。
• 先验证再更新：先用SELECT语句验证转换结果，确认无误后再执行UPDATE。
• 字段类型匹配：确保目标字段correct_ts的类型与转换结果兼容（如Timestamp类型）。

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