PostgreSQL 中带/不带时区的时间戳之间的差异

Difference between timestamps with/without time zone in PostgreSQL

提问by Larsenal

Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONEversus WITHOUT TIME ZONE? Can the differences be illustrated with simple test cases?

当数据类型为WITH TIME ZONEvs时，时间戳值在 PostgreSQL 中的存储方式是否不同WITHOUT TIME ZONE？可以用简单的测试用例说明差异吗？

采纳答案by bignose

The differences are covered at the PostgreSQL documentation for date/time types. Yes, the treatment of TIMEor TIMESTAMPdiffers between one WITH TIME ZONEor WITHOUT TIME ZONE. It doesn't affect how the values are stored; it affects how they are interpreted.

日期/时间类型的 PostgreSQL 文档中介绍了这些差异。是的，TIME或的处理方式TIMESTAMP在 1WITH TIME ZONE或之间有所不同WITHOUT TIME ZONE。它不会影响值的存储方式；它会影响它们的解释方式。

The effects of time zones on these data types is covered specificallyin the docs. The difference arises from what the system can reasonably know about the value:

文档中专门介绍了时区对这些数据类型的影响。不同之处在于系统可以合理地了解该值：

• With a time zone as part of the value, the value can be rendered as a local time in the client.

• Without a time zone as part of the value, the obvious default time zone is UTC, so it is rendered for that time zone.

• 将时区作为值的一部分，该值可以在客户端呈现为本地时间。

• 如果没有时区作为值的一部分，明显的默认时区是 UTC，因此它是针对该时区呈现的。

The behaviour differs depending on at least three factors:

行为的不同取决于至少三个因素：

• The timezone setting in the client.
• The data type (i.e. WITH TIME ZONEor WITHOUT TIME ZONE) of the value.
• Whether the value is specified with a particular time zone.

• 客户端中的时区设置。
• 值的数据类型（即WITH TIME ZONE或WITHOUT TIME ZONE）。
• 该值是否使用特定时区指定。

Here are examples covering the combinations of those factors:

以下是涵盖这些因素组合的示例：

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)

回答by ddekany

I try to explain it more understandably than the referred PostgreSQL documentation.

我试图比引用的 PostgreSQL 文档更容易理解它。

Neither TIMESTAMPvariants store a time zone (or an offset), despite what the names suggest. The difference is in the interpretation of the stored data (and in the intended application), not in the storage format itself:

TIMESTAMP尽管名称暗示了什么，这两个变体都不会存储时区（或偏移量）。区别在于存储数据的解释（以及预期的应用程序），而不是存储格式本身：

• TIMESTAMP WITHOUT TIME ZONEstores localdate-time (aka. wall calendar date and wall clock time). Its time zone is unspecified as far as PostgreSQL can tell (though your application may knows what it is). Hence, PostgreSQL does no time zone related conversion on input or output. If the value was entered into the database as '2011-07-01 06:30:30', then no mater in what time zone you display it later, it will still say year 2011, month 07, day 01, 06 hours, 30 minutes, and 30 seconds (in some format). Also, any offset or time zone you specify in the input is ignored by PostgreSQL, so '2011-07-01 06:30:30+00'and '2011-07-01 06:30:30+05'are the same as just '2011-07-01 06:30:30'. For Java developers: it's analogous to java.time.LocalDateTime.

• TIMESTAMP WITH TIME ZONEstores a point on the UTC time line. How it looks (how many hours, minutes, etc.) depends on your time zone, but it always refers to the same "physical" instant (like the moment of an actual physical event). The input is internally converted to UTC, and that's how it's stored. For that, the offset of the input must be known, so when the input contains no explicit offset or time zone (like '2011-07-01 06:30:30') it's assumed to be in the current time zone of the PostgreSQL session, otherwise the explicitly specified offset or time zone is used (as in '2011-07-01 06:30:30+05'). The output is displayed converted to the current time zone of the PostgreSQL session. For Java developers: It's analogous to java.time.Instant(with lower resolution though), but with JDBC and JPA 2.2 you are supposed to map it to java.time.OffsetDateTime(or to java.util.Dateor java.sql.Timestampof course).

• TIMESTAMP WITHOUT TIME ZONE存储本地日期时间（又名挂历日期和挂钟时间）。就 PostgreSQL 而言，它的时区是未指定的（尽管您的应用程序可能知道它是什么）。因此，PostgreSQL 不会对输入或输出进行与时区相关的转换。如果该值作为 输入到数据库中'2011-07-01 06:30:30'，那么无论您稍后在哪个时区显示它，它仍会显示 2011 年、07 月、01 日、06 小时、30 分钟和 30 秒（以某种格式）。此外，您在输入中指定的任何偏移量或时区都会被 PostgreSQL 忽略，因此'2011-07-01 06:30:30+00'和'2011-07-01 06:30:30+05'与'2011-07-01 06:30:30'. 对于 Java 开发人员：它类似于java.time.LocalDateTime.

• TIMESTAMP WITH TIME ZONE在 UTC 时间线上存储一个点。它的外观（多少小时、多少分钟等）取决于您所在的时区，但它始终指的是同一个“物理”时刻（例如实际物理事件的时刻）。输入在内部转换为 UTC，这就是它的存储方式。为此，必须知道输入的偏移量，因此当输入不包含显式偏移量或时区（如'2011-07-01 06:30:30'）时，假定它位于 PostgreSQL 会话的当前时区，否则使用明确指定的偏移量或时区（如'2011-07-01 06:30:30+05'）。输出显示为转换为 PostgreSQL 会话的当前时区。对于 Java 开发人员：它类似于java.time.Instant（尽管分辨率较低），但是对于 JDBC 和 JPA 2.2，您应该将其映射到java.time.OffsetDateTime（或映射到java.util.Date或java.sql.Timestamp当然）。

Some say that both TIMESTAMPvariations store UTC date-time. Kind of, but it's confusing to put it that way in my opinion. TIMESTAMP WITHOUT TIME ZONEis stored like a TIMESTAMP WITH TIME ZONE, which rendered with UTC time zone happens to give the same year, month, day, hours, minutes, seconds, and microseconds as they are in the local date-time. But it's not meant to represent the point on the time line that the UTC interpretation says, it's just the way the local date-time fields are encoded. (It's some cluster of dots on the time line, as the real time zone is not UTC; we don't know what it is.)

有人说这两种TIMESTAMP变体都存储 UTC 日期时间。有点，但在我看来这样说很令人困惑。TIMESTAMP WITHOUT TIME ZONE像 a 一样存储TIMESTAMP WITH TIME ZONE，它以 UTC 时区呈现，恰好给出与本地日期时间相同的年、月、日、小时、分钟、秒和微秒。但这并不意味着代表 UTC 解释所说的时间线上的点，这只是本地日期时间字段的编码方式。（这是时间线上的一些点簇，因为实际时区不是 UTC；我们不知道它是什么。）

回答by serby

Here is an example that should help. If you have a timestamp with a timezone, you can convert that timestamp into any other timezone. If you haven't got a base timezone it won't be converted correctly.

这是一个应该有帮助的例子。如果您有一个带时区的时间戳，您可以将该时间戳转换为任何其他时区。如果您没有基本时区，它将无法正确转换。

SELECT now(),
   now()::timestamp,
   now() AT TIME ZONE 'CST',
   now()::timestamp AT TIME ZONE 'CST'

Output:

输出：

-[ RECORD 1 ]---------------------------
now      | 2018-09-15 17:01:36.399357+03
now      | 2018-09-15 17:01:36.399357
timezone | 2018-09-15 08:01:36.399357
timezone | 2018-09-16 02:01:36.399357+03