Oracle带Meridian标识的Datetime转PostgreSQL及插入方法咨询

PostgreSQL侧推荐的数据类型 #

• 无需时区信息：使用timestamp（全称timestamp without time zone），支持微秒级精度，完全覆盖你的毫秒级时间需求，存储本地时间。
• 需要时区支持：使用timestamptz（全称timestamp with time zone），存储时自动转换为UTC，查询时根据会话时区返回对应时间。

插入时的格式转换方法 #

Oracle的目标时间格式为DD-MON-RR HH.MI.SS.FFF AM，PostgreSQL可通过to_timestamp()函数指定格式模板解析该字符串：

核心转换逻辑 #

格式模板对应关系：

• DD：两位日期数字
• MON：英文月份缩写（与Oracle的SEP匹配）
• RR：两位年份（兼容Oracle的年份自动补全逻辑）
• HH12：12小时制小时数
• MI：分钟数
• SS：秒数
• MS：毫秒数（对应示例中的.225）
• AM：上午/下午标识

插入示例SQL #

假设目标表为target_table，时间字段为datetime_col：

-- 单条插入
INSERT INTO target_table (datetime_col)
VALUES (to_timestamp('29-SEP-22 12.00.17.225 AM', 'DD-MON-RR HH12.MI.SS.MS AM'));

-- 批量插入（假设数据来自临时表或导出的数据集）
INSERT INTO target_table (datetime_col)
SELECT to_timestamp(oracle_time_str, 'DD-MON-RR HH12.MI.SS.MS AM')
FROM source_data;

注意事项 #

• 确保PostgreSQL的lc_time参数为英文环境（如en_US.UTF8），否则无法识别英文月份缩写。若环境不符，可临时设置：

  SET lc_time = 'en_US.UTF8';
  

• 如果毫秒部分是6位（微秒），将格式模板中的MS替换为US即可。

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