PostgreSQL如何从Unix时间戳字符串字段获取日期时间？

我执行以下查询：

select
(featuremap ->> 'column.time:value')
from db.table;

该查询返回字符串类型的Unix时间戳。尝试两种方法均报错：

第一种方法：

select
extract(epoch from(featuremap ->> 'column.time:value'))
from db.table;

报错信息：ERROR: function pg_catalog.date_part(unknown, text) does not exist

第二种方法：

select
to_timestamp(featuremap ->> 'column.time:value')
from db.table;

报错信息：ERROR: function to_timestamp(text) does not exist

请问该如何从这个字符串类型的Unix时间戳字段中获取日期时间？

核心问题是->>操作符返回文本类型，而to_timestamp()和extract()仅接受数值类型参数，必须先将字符串转换为数值。

方法1：直接转换为日期时间 #

根据时间戳是秒级还是毫秒级选择对应转换方式：

-- 秒级时间戳（10位数字）
select to_timestamp((featuremap ->> 'column.time:value')::bigint)
from db.table;

-- 毫秒级时间戳（13位数字，需除以1000转为秒）
select to_timestamp((featuremap ->> 'column.time:value')::bigint / 1000.0)
from db.table;

方法2：提取时间字段（如年、月） #

若需提取时间中的特定部分，先转成时间类型再用extract：

select extract(year from to_timestamp((featuremap ->> 'column.time:value')::bigint))
from db.table;

容错处理（可选） #

如果存在无效的时间戳字符串，PostgreSQL 12+ 可使用try_cast避免报错：

select to_timestamp(try_cast(featuremap ->> 'column.time:value' as bigint))
from db.table;

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