PostgreSQL中Unix毫秒时间戳转TIMESTAMP报错及解决方法

将MongoDB中的表导入PostgreSQL数据库后，解析JSON得到日期列startDate，其值为Unix毫秒时间戳字符串1672690270000，希望转换为ISO格式2023-01-02 20:11:10.000。当前PostgreSQL的datestyle设置为ISO, MDY。尝试执行以下语句：

Select TO_TIMESTAMP(startDate, 'YYYY/MM/DD HH24:MI:SS')

收到错误：

ERROR: value for "YYYY" in source string is out of range
DETAIL: Value must be in the range -2147483648 to 2147483647.
SQL state: 22008

你误用了TO_TIMESTAMP的用法：这个函数的第二个参数是用来解析日期格式字符串的，比如'YYYY/MM/DD HH24:MI:SS'是告诉PostgreSQL“把第一个参数当成这种格式的日期字符串解析”。但你的startDate是毫秒时间戳字符串1672690270000，PostgreSQL会把这个字符串的前几位匹配到YYYY（年份）的位置，1672690270000远超出了PostgreSQL支持的年份范围，因此抛出该错误。

有两种可靠方式完成转换：

方法1：使用TO_TIMESTAMP_MS（PostgreSQL 12及以上版本支持） #

该函数直接支持毫秒级Unix时间戳，先将字符串转成数值类型再传入：

SELECT TO_CHAR(TO_TIMESTAMP_MS(startDate::BIGINT), 'YYYY-MM-DD HH24:MI:SS.FFF') AS formatted_startDate;

• startDate::BIGINT：把字符串类型的毫秒时间戳转为大整数
• TO_TIMESTAMP_MS：将毫秒时间戳转为timestamp类型
• TO_CHAR：将timestamp格式化为目标ISO格式字符串

方法2：兼容低版本PostgreSQL（手动转秒） #

如果你的PostgreSQL版本低于12，可将毫秒时间戳除以1000转成秒级，再用TO_TIMESTAMP：

SELECT TO_CHAR(TO_TIMESTAMP(startDate::BIGINT / 1000.0), 'YYYY-MM-DD HH24:MI:SS.FFF') AS formatted_startDate;

• startDate::BIGINT / 1000.0：把毫秒转成带小数的秒（保留毫秒精度）
• TO_TIMESTAMP：处理秒级时间戳（支持小数，对应毫秒部分）
• TO_CHAR：格式化为目标字符串

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