PostgreSQL 与时间戳之谜

Question

我认为 IT 的两个祸害之一是时间戳和时区(另一个是字符编码)，人们不断地在其中绊倒...

在这方面，我目前遇到一个与存储到 PostgreSQL 数据库中的 Java 应用程序中的不同时间戳相关的问题。

为简单起见，假设有下表:

CREATE TABLE ts_test
(
  id integer NOT NULL,
  utc timestamp without time zone,
  local timestamp with time zone,
  CONSTRAINT pk PRIMARY KEY (id)
)

所以，我必须存储一个 UTC 时间戳和一个本地时间戳，在我的例子中是中欧夏令时，所以目前是 UTC+2。

进一步假设表中有 2 个条目，在 psql 控制台上输出如下(数据库以 UTC 运行):

# select id,  utc, local, local-utc as diff from ts_test;
 id |         utc         |         local          |   diff   
----+---------------------+------------------------+----------
  1 | 2012-06-27 12:00:00 | 2012-06-27 12:00:00+00 | 00:00:00
  2 | 2012-06-27 12:00:00 | 2012-06-27 14:00:00+00 | 02:00:00
(2 rows)

现在，出现了几个问题:

• 本地列中的输出到底是什么意思？
• 系统如何知道我插入值的时区？
• 如何查看存储的真实原始值(例如毫秒)？

我会假设，第一行的本地“12:00:00+00”意味着它是 UTC 中的 12:00，而在 CEST 中又是 14:00。但似乎(我们的数据库管理员告诉我)，第二行的本地“14:00:00+00”是 14:00 CEST 的正确值——由 2 小时的差异支持。

但是要通过 sql 插入生成第二行，我必须写

insert into ts_test (id, utc, local) values (2, '2012-06-27 12:00:00', '2012-06-27 16:00:00+02');

这又不支持谓词。

所以，总结一下这个长长的问题 - 任何人都可以告诉我这整个过程的详细工作原理、输出应该意味着什么以及应该如何将本地时间戳正确写入数据库？

Answer 1

根据 local 列的输出，您的 SQL session 的时区设置为 UTC 或 GMT 而不是时间您居住的区域。大概这就是您的意思:数据库以 UTC 运行。这是问题的根源，但让我们尝试详细说明。

作为数据存储库的数据库本身没有时区，但每个 SQL session 都有自己的时区。

当它们被 SQL session 请求时，timestamp without time zone 的值不旋转到 session 的时区并且不 显示时间偏移量，而 timestamp with time zone 的值将旋转到 session 的时区并显示该时区的时间偏移量。这就是两者的区别。

时区永远不会存储在任何数据类型中，因为在读取值时，重要的是请求该值的 SQL session 的时区。

将您的 SQL 时区设置为 UTC 不是一个好主意，因为它与您问题的其他部分相矛盾:

So, I have to store a UTC time stamp and a local one, which in my case is central Europe summer time, so currently UTC+2

让 SQL session 知道您的实时时区，它将开始按预期工作。如果不这样做，timestamp with time zone 基本上是无用的。

另请注意，在 utc timestamp without time zone 和 local timestamp with time zone 中存储相同的时间是没有意义的，因为您总能得到 utc 与:

SELECT local AT TIME ZONE 'UTC' FROM ts_test WHERE...

编辑:评论中问题的答案:

Q: you are saying that if my timezone is set to my local time in the session, then I should see for example ...14:00:00+02 in local for a utc value of ...12:00:00

是的。

Q: And when writing something into the local field from my application, it matters which timezone is set there?

没错。

Q: How does one set this in JDBS-session?

我不知道 JDBC，但在 SQL 级别，例如:

 SET timezone='Europe/Berlin';

通常它是从环境中自动设置的，但它可以在各种级别强制设置，包括 postgresql.conf。在 session 中显式设置它会覆盖任何其他内容。

Q: can I see the raw value of the timestamp somehow, to make sure it's not just a representation problem when displaying

除了 pageinspect，我不知道该怎么做它在较低级别运行。