如何将毫秒值转换为带小数的PostgreSQL Timestamp格式

针对你的问题，以下是两种可靠的转换方式，确保得到带微秒精度的timestamp格式：

方法1：使用专用毫秒转换函数（PostgreSQL 12+） #

PostgreSQL 12及以上版本提供了TO_TIMESTAMP_MS()函数，可直接将毫秒级UNIX时间戳转换为带时区的时间，再转换为timestamp类型存入表中：

INSERT INTO my_table (created) VALUES (TO_TIMESTAMP_MS(1706812200000)::timestamp);

执行后，created字段的值会是2024-02-02 01:30:00.000000（UTC时区下），完全符合你想要的YYYY-MM-DD HH:MI:SS.FFFFFF格式。

方法2：兼容低版本PostgreSQL #

如果你的PostgreSQL版本低于12，没有TO_TIMESTAMP_MS函数，可以通过精确的数值转换避免精度丢失：

INSERT INTO my_table (created) VALUES (TO_TIMESTAMP(1706812200000::bigint / 1000.0));

这里先将毫秒值转为bigint保证整数精度，再除以1000.0得到带小数的秒级时间戳，传入TO_TIMESTAMP后同样会生成带微秒精度的结果。

时区说明 #

如果你得到的时间与预期时区不符，可以检查数据库的timezone配置，或者在转换时指定时区，例如：

INSERT INTO my_table (created) VALUES (TO_TIMESTAMP_MS(1706812200000) AT TIME ZONE 'Asia/Shanghai');

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