Postgresql和Java之间UTC偏移量存在一小时差异

Question

我有一个Java程序，它以PostgreSQL二进制格式生成时区值，以便在没有时区的情况下向包含时间戳列的表中插入数据。生成的数据被写入一个二进制文件，然后使用COPY命令将其插入到PostgreSQL中。

格式实际上是将数字8作为4字节值，后跟自2000年1月1日以来的微秒数作为8字节值。

我发现PostgreSQL和Java对时区偏移量的解释存在差异。当我尝试写入日期时：

2004-11-01 09:34:42.432

在Postgres二进制格式中，它是这样的

0x00, 0x00, 0x00, 0x08, 0x00, 0x00, 0x8a, 0xcd, 0xe3, 0x10, 0x68, 0x00

并且Postgres正确地报告了日期。然而，如果我输入日期

2010-11-01 09:34:42.432

在二进制中表示为

0x00, 0x00, 0x00, 0x08, 0x00, 0x01, 0x36, 0xf8, 0x72, 0xcb, 0x64, 0x00

我在Postgres中得到了2010-11-01 08:34:42.432作为时间戳。

进一步调查表明，Postgres认为2010年的UTC偏移量为-5，而Java认为其为-4（我相信这是正确的），导致了一个小时的差异。有人知道解决方法吗？

相关信息：

Postgresql版本9.2.4

Java：7

工作于美国东部时区（America/New_York）。

操作系统：Linux（时区设置正确）。

- Καrτhικ

Answer 1

我搞定了。原来Postgres期望的时间戳列中的微秒是自2000年1月1日UTC以来的微秒数。换句话说，需要将当前时间与Jan 1 2000 UTC相减，而不是将当前时间与当地的Jan 1 2000相减。我之前做的是后者。

为帮助其他人提供额外信息：

1. 如果您的列是没有时区的时间戳，请发送从当前时间到Jan 1 2000 UTC的微秒数加上微秒级的tz偏移量。 2. 如果您的列是有时区的时间戳，请发送从当前时间到Jan 1 2000 UTC的微秒数。

(1) 实际上就是在UTC下的当前时间（即使您正在尝试本地时间9:00 am，如果偏移量为-3，则不要将其变成12:00 pm UTC，而是将其编码为9:00 am UTC）减去Jan 1 2000 UTC。重要的事实是这与（当地时区的当前时间）-（当地时区的Jan 1 2000）不同。

无偿推广我有一个Java库，可直接使用JPA注释实体的集合，使用COPY命令将行直接插入到Postgresql中。 可在github上找到：https://github.com/eclecticlogic/pedal-dialect

- Καrτhικ

Answer 2

从Linux命令行进行的快速测试表明，美国/纽约的2010年11月1日是EDT（-4）。您在Postgresql和Java中是否具有相同的时区规范？您使用哪个Java版本 - 它们使用相同的TZif数据库，但可能不同版本。如果您使用Postgresql 9.2.4或更高版本进行测试，是否会获得相同的结果？

- araqnid

---

我更新了问题以反映正确的Postgres版本：9.2.4。 - Καrτhικ

我该如何在Postgres中检查时区规范？有特定的命令吗？ - Καrτhικ

9.2.4版本具有tzinfo 2013b版本（摘自发布说明）。我在Debian上安装了2013c版本，但是2013b和2013c之间的差异似乎没有涉及到这个问题。 - araqnid

至少在Debian上，我认为postgresql使用系统的时区数据而不是拥有其自己的冗余包装。 - araqnid

我认为问题出在Java端。我的测试工具报告2013-11-14的偏移量为-04，这显然是错误的。进一步调查...编辑..用户错误。Java端的时区是正确的（-05）。 - Καrτhικ