sql - Postgresql to_timestamp 在包装 extract() 时返回不同的日期

Question

导入脚本写得有点错误，导致时间戳被插入了 1000 倍。然而，将 to_timestamp 与 extract() 一起使用会导致大约一个月的日期，即使中间数字和转换看起来是正确的。

1)
select start_time from matches order by match_id limit 1;

  | timestamp without time zone
----------------------
1 | 1970-01-16 11:29:18.352


2)
select (extract(epoch from start_time) * 1000)
  from matches order by match_id limit 1;

  | double precision
----------------------
1 | 1333758352


3)
select to_timestamp(1333758352) at time zone 'UTC';

  | timestamp without time zone
----------------------
1 | 2012-04-07 00:25:52


4)
select (to_timestamp(extract(epoch from start_time) * 1000) at time zone 'UTC')
  from matches order by match_id limit 1;

  | timestamp without time zone
----------------------
1 | 2012-05-18 16:25:52

1) 在时间戳列中显示当前数据，2) 和 3) 显示正确的数字和正在执行的转换，但在 4) 中的一个查询中放在一起，日期最终偏移了一个多月。

我对可能出现的问题有点困惑，因为组件单独工作，并且 to_timestamp 中的简单数学在我尝试过的其他情况下也有效。

---

重现(PostgreSQL 9.3.1):

create table test_table ( t timestamp );
insert into test_table VALUES ('1970-01-16 11:29:18.352');
select (extract(epoch from t) * 1000) from test_table;
select to_timestamp(1333758352) at time zone 'UTC';
select (to_timestamp(extract(epoch from t) * 1000) at time zone 'UTC') from test_table;

SqlFiddle:http://sqlfiddle.com/#!15/12f6f/1/0

在 SqlFiddle 上它适用于 8.4，但在 9.3 上失败。

---

解决及原因

这里的天真方法遇到了一个边缘情况，我们最终将一小时的时区偏移量与原始日期相乘。所以结果是 1000 小时的偏移量(由于我在格林威治标准时间)。将非 UTC 时间转换为时区会降低 GMT 转换，从而让日期的奇数乘法按预期进行。

因此，extract(epoch from t::timestamp with time zone) 对结果进行原始数学运算，然后将其放回时间戳中。

Answer 1

8.x 和 9.x 版本之间的行为肯定存在差异。 ( fiddle )

select 
    start_time
  , extract(epoch from start_time) * 1000 as epoch_start_time
  , to_timestamp(1337358352) at time zone 'UTC' as to_timestamp_int
  , to_timestamp(extract(epoch from start_time) * 1000) at time zone 'UTC' as to_timestamp_expression
from matches;

8.x 显示您的行为。 9.x 显示我的“无法重现”行为。 (您可以在 SQLfiddle 的工具栏中切换版本。)