PostgreSQL：拼接补零转换Unix时间戳后时间与预期不符的问题

DevOps 小满

2026-7-18

问题分析

你遇到的时间差3小时的问题，本质是时区不一致导致的：

to_timestamp()函数会将Unix时间戳（秒数）解释为UTC时间，然后自动转换为当前数据库会话的时区时间。
你浏览器验证的结果是UTC时间，而数据库会话使用的是UTC+3时区，所以转换后时间比正确结果快了3小时。

另外你的SQL可以优化：子查询里的order by是多余的，因为外层查询的排序应该放在最后，子查询的排序不会影响最终结果（除非配合limit）。

解决方案

方法1：直接转换为UTC时间

使用AT TIME ZONE 'UTC'子句，将to_timestamp()返回的带时区时间转换为UTC时区的本地时间：

SELECT 
  to_timestamp(cast(concat("timestamp", '0')::double precision / 1000 AS bigint)) AT TIME ZONE 'UTC' AS ts,
  col1, col2, "timestamp"
FROM table1
ORDER BY col1 DESC;

方法2：临时修改会话时区为UTC

在执行查询前设置会话时区为UTC，这样to_timestamp()直接返回UTC时间：

SET TIME ZONE 'UTC';

SELECT 
  to_timestamp(cast(concat("timestamp", '0')::double precision / 1000 AS bigint)) AS ts,
  col1, col2, "timestamp"
FROM table1
ORDER BY col1 DESC;

方法3：转换为指定时区（如果需要）

如果你需要的不是UTC，而是特定时区（比如东八区），可以替换时区标识：

SELECT 
  to_timestamp(cast(concat("timestamp", '0')::double precision / 1000 AS bigint)) AT TIME ZONE 'Asia/Shanghai' AS ts,
  col1, col2, "timestamp"
FROM table1
ORDER BY col1 DESC;

验证说明

以你的示例时间戳168665720100为例：