带有时间戳字段的 PostgreSQL 神秘主义

Question

我有带有timestamp with time zone 的PostgreSQL 数据库表名为 published_date 的字段。

我使用 psql -U postgres dbname 命令访问我的数据库。

当我尝试通过命令更新 published_date 字段值时:

UPDATE mytable SET published_date='PUBLISHED_DATE' WHERE id=3;

我得到了相应 PUBLISHED_DATE 的以下结果:

SOURCE              -> RESULT

2013-01-16 20:00:00 -> 2013-01-16 20:00:00+00 (WELL)
2013-02-16 20:00:00 -> 2013-02-16 20:00:00+00 (WELL)
2013-03-16 20:00:00 -> 2013-03-16 20:00:00+00 (WELL)
2013-04-16 20:00:00 -> 2013-04-16 20:00:00+01 (STRANGE!)
2013-05-16 20:00:00 -> 2013-05-16 20:00:00+01 (STRANGE!)
2013-06-16 20:00:00 -> 2013-06-16 20:00:00+01 (STRANGE!)
2013-07-16 20:00:00 -> 2013-07-16 20:00:00+01 (STRANGE!)
2013-08-16 20:00:00 -> 2013-08-16 20:00:00+01 (STRANGE!)
2013-09-16 20:00:00 -> 2013-09-16 20:00:00+01 (STRANGE!)
2013-10-16 20:00:00 -> 2013-10-16 20:00:00+01 (STRANGE!)
2013-11-16 20:00:00 -> 2013-11-16 20:00:00+00 (WELL)
2013-12-16 20:00:00 -> 2013-12-16 20:00:00+00 (WELL)

数据库中有UTC时区:

=> SHOW timezone;
 TimeZone 
----------
 UTC
(1 row)

和系统:

$ date
Tue Dec 17 00:14:12 GMT 2013

PostgreSQL 版本为 9.2.4

相同版本的 postgres 在另一台机器上安装了相同的标志，运行良好!很奇怪。

Answer 1

这是正常行为，PostgreSQL 在输出带时区的时间戳(内部确实存储为 UTC)时会尝试容纳用户。我假设你在英国，你看到的是它在夏季和冬季之间的变化。从手册中强调:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).