PostgreSQL中整数转timestamp：年份显示异常问题求助

我有一张表，包含三列：id（int类型）、timestamp（int类型）、username（varchar类型）。尝试将timestamp转换为日期格式或标准时间戳时，年份显示异常。以下是我试过的SQL语句：

select
    table.id,
    table.timestamp,
    to_timestamp(table.timestamp),
    table.username
from table

（配图：转换结果中年份显示为4997等异常值）

select
    table.id,
    table.timestamp,
    to_char(to_timestamp(table.timestamp), 'dd-mm-yyyy'),
    table.username
from table

问题根源是时间戳单位不匹配：PostgreSQL的to_timestamp()默认处理秒级时间戳（从1970-01-01 00:00:00 UTC起的秒数），但你的timestamp字段存储的是毫秒级时间戳（毫秒数）。直接传入毫秒数会让时间被放大1000倍，导致年份异常。

修正后的SQL需要先将毫秒级时间戳转为秒级（用浮点数除法避免整数截断）：

select
    id,
    timestamp,
    to_timestamp(timestamp / 1000.0) as standard_timestamp,
    to_char(to_timestamp(timestamp / 1000.0), 'dd-mm-yyyy') as formatted_date,
    username
from "table"; -- table是SQL关键字，用双引号包裹避免语法错误

如果你的时间戳是其他单位（比如微秒），调整除法基数即可（如微秒除以1000000）。

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