如何在更新中将 bigint 转换为 postgres 中的时区时间戳

Question

有没有办法在 Postgres 中将 BIGINT 转换为 TIMESTAMP 或 TIMESTAMP WITH TIME ZONE ？我需要将数据从 BIGINT 列复制到 TIMESTAMP 列。

这是我尝试过的：

 update table
set date__timestamp = date__bigint::timestamp
where foo = 1;

错误：无法将类型 bigint 转换为没有时区的时间戳

我将时间戳列更改为带有时区的列并尝试了以下操作：

 update table
set date__timestamp = date__bigint::timestamp with time zone at time zone 'PST'
where foo = 1;

错误：无法将类型 bigint 转换为带时区的时间戳

update table
set date__timstamp = TO_CHAR(TO_TIMESTAMP(date__bi / 1000), 'DD/MM/YYYY HH24:MI:SS')
where foo = 1;

错误：列“date__timestamp”是没有时区的时间戳类型，但表达式是文本类型提示：您需要重写或强制转换表达式。

数据看起来像这样。

 date_bigint: 20181102
date_timestamp: 2018-11-02 17:00:00.000000

我需要将默认值传递给强制转换吗？

原文由 brettu 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以将其转换为文本并使用 TO_TIMESTAMP 并转换为 timestamp at time zone

 SELECT
to_timestamp ( '20181102'::bigint::text,'YYYYMMDD')::timestamp at time zone 'UTC'
at time zone 'PST' ;

update t
   set date__timestamp = TO_TIMESTAMP(date_bigint::text,'YYYYMMDD')::timestamp
   at time zone 'UTC' at time zone 'PST'
where foo = 1;

演示

原文由 Kaushik Nayak 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

此答案假定 date__bigint 列存储自纪元以来的 UNIX 时间戳（以秒为单位）。这是转换为 Postgres 时间戳的一种方法：

 UPDATE your_table
SET date__timestamp = TIMESTAMP 'epoch' + date__bigint * INTERVAL '1 second'
WHERE foo = 1;

也就是说，我们可以在纪元时间戳中添加一些秒数，以将您的值转换为正式的 Postgres 时间戳。

原文由 Tim Biegeleisen 发布，翻译遵循 CC BY-SA 4.0 许可协议