PostgreSQL使用to_timestamp转换时间字段时报12小时制小时无效错误
问题定位
当前SQL的格式模板存在两类互斥规则的冲突,直接触发校验报错:
- 格式串中使用
HH24格式符,代表需要解析取值范围为00-23的24小时制小时 - 格式串中额外追加了
PM格式符,该格式符是12小时制时间的配套标识,作用是让数据库按照12小时制规则校验小时值(要求小时取值在01-12区间内)
当两类互斥的格式符同时出现在模板中时,数据库会自动切换为12小时制的校验逻辑,读取到字符串中的16(超出1-12的12小时制合法范围),就会抛出对应的小时值无效报错。
另外需要明确:存储的原始字符串8/31/2020-16:29:31 PM尾部的PM是完全冗余的内容——24小时制的16已经明确对应下午4点,该标识没有任何时间解析价值。不少开发者看到字符串尾部带PM文本,就会下意识在格式模板中追加PM格式符,忽略了该格式符和HH24的互斥关系,这是触发该类报错的常见原因:在当前的错误写法中,PM被数据库识别为12小时制的特殊解析标记,触发了12小时制的校验逻辑。
正确转换写法
根据原始字符串的结构,可选择两种兼容写法完成时间转换:
- 完整匹配整个字符串(包含尾部冗余PM文本):将格式串中作为特殊解析规则的
PM替换为固定匹配的普通文本(用双引号包裹,避免数据库将其识别为AM/PM格式符):
SELECT TO_TIMESTAMP(submitted_date, 'MM/DD/YYYY-HH24:MI:SS "PM"') FROM table;
- 简化匹配有效时间部分:由于数据库的
to_timestamp函数在解析时,会自动忽略格式串匹配完成后剩余的无关文本,可直接去掉格式串中多余的PM部分,仅匹配时间的有效结构:
SELECT TO_TIMESTAMP(submitted_date, 'MM/DD/YYYY-HH24:MI:SS') FROM table;
内容的提问来源于stack exchange,提问作者Prasanna
相关产品推荐
相关产品推荐

