如何在PostgreSQL中编写通用语句转换任意格式时间戳？

针对你遇到的格式随机时间字符串转PostgreSQL标准格式问题，直接用以下通用语句即可解决：

核心转换代码 #

假设存储原始时间的字段为raw_time，目标表为your_table：

SELECT 
  to_char(
    try_to_timestamp(raw_time, 'MM/DD/YYYY HH24.MI.SS|MM/DD/YYYY HH24:MI:SS'),
    'YYYY-MM-DD HH12:MI:SS AM'
  ) AS standard_timestamp
FROM your_table;

关键逻辑说明 #

• 多格式兼容：try_to_timestamp的格式参数用竖线|分隔多个匹配模板，会自动适配你给出的两种格式（时间分隔符为.或:）。
• 24小时转12小时制：先用HH24解析原始24小时时间，再通过HH12输出12小时制，加上AM/PM明确区分上下午（不需要的话可去掉AM）。
• 避免报错：try_to_timestamp（PostgreSQL 12+支持）会在格式不匹配时返回NULL，而非抛出「无效日期格式」错误；如果用低版本，可通过正则过滤后再转换：

SELECT 
  CASE 
    WHEN raw_time ~ '^\d{1,2}/\d{1,2}/\d{4} \d{2}([.:])\d{2}\1\d{2}$'
    THEN to_char(to_timestamp(raw_time, 'MM/DD/YYYY HH24.MI.SS|MM/DD/YYYY HH24:MI:SS'), 'YYYY-MM-DD HH12:MI:SS AM')
    ELSE NULL
  END AS standard_timestamp
FROM your_table;

扩展适配 #

如果数据中还存在DD/MM/YYYY的日期格式，只需在格式模板中追加DD/MM/YYYY HH24.MI.SS|DD/MM/YYYY HH24:MI:SS即可，但注意这种情况和MM/DD/YYYY可能存在歧义（如05/10/2022可被解析为两种日期），需根据实际业务规则调整优先级。

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