PostgreSQL如何将带AM/PM的文本时间转换为timestamp时间戳

原有语句失效原因 #

你写的转换语句存在两个核心错误：

• 格式模板与原始值结构不匹配：原始存储值为月/日/年 12小时制时间 上下午标识结构，你编写的模板是年-月-日顺序，无法对应解析
• 格式符使用错误：
  • PostgreSQL中匹配AM/PM标识的格式符为am（不区分大小写，可自动匹配am/pm、AM/PM各类写法），不是p
  • 搭配AM/PM标识解析时间时，必须使用HH12（可简写为HH）代表12小时制小时，不能用HH24，否则会出现小时计算错误

正确实现方案 #

1. 转换为标准timestamp类型 #

如果需要得到原生timestamp类型值，使用如下语句即可：

SELECT to_timestamp(created, 'MM/DD/YYYY HH:MI am') AS create_ts
FROM 你的业务表名;

转换得到的timestamp类型值默认以24小时制标准时间格式存储和展示，可直接参与时间计算、比较等操作。

2. 直接输出YYYY-MM-DD HH24:MI:SS格式字符串 #

如果需要直接得到固定格式的字符串结果，可以嵌套to_char函数做格式化：

SELECT to_char(
  to_timestamp(created, 'MM/DD/YYYY HH:MI am'),
  'YYYY-MM-DD HH24:MI:SS'
) AS formatted_create_time
FROM 你的业务表名;

兼容说明 #

上述模板可自动兼容字段中无前置零的非规整格式，类似6/9/2022 9:05 am这类单数字月、日、小时的值，都可以正常解析，不需要额外做字符串补零处理。

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