PostgreSQL中转换字符串时间戳并删除一年前数据的问题

你遇到的核心问题是没有先将字符类型的Unix时间戳转换为数值类型，直接调用to_timestamp()会导致函数解析逻辑错误（to_timestamp()接收字符串时会尝试解析为日期格式，而非Unix时间戳）。以下是正确的操作步骤：

1. 先验证转换逻辑（避免误删） #

先执行查询确认哪些数据符合删除条件，确保转换后的时间正确：

SELECT 
    your_timestamp_column,
    to_timestamp(CAST(your_timestamp_column AS BIGINT)) AS converted_datetime
FROM your_table
WHERE to_timestamp(CAST(your_timestamp_column AS BIGINT)) < NOW() - INTERVAL '1 year';

替换your_timestamp_column为你的列名，your_table为表名。

2. 执行删除操作 #

确认结果无误后，执行删除语句：

DELETE FROM your_table
WHERE to_timestamp(CAST(your_timestamp_column AS BIGINT)) < NOW() - INTERVAL '1 year';

额外建议 #

如果后续需要频繁对该列进行时间操作，建议修改列的类型，避免每次查询都要转换：

-- 先转换列内数据为数值类型
ALTER TABLE your_table ALTER COLUMN your_timestamp_column TYPE BIGINT USING CAST(your_timestamp_column AS BIGINT);
-- 再转换为timestamp类型（可选，如果你想直接存储时间而非Unix戳）
ALTER TABLE your_table ALTER COLUMN your_timestamp_column TYPE TIMESTAMP USING to_timestamp(your_timestamp_column);

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