PostgreSQL 中带/不带时区的时间戳之间的差异 [英] Difference between timestamps with/without time zone in PostgreSQL

问题描述

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

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

推荐答案

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

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

时区对这些数据类型的影响是文档中专门介绍了.差异源于系统对值的合理了解:

The effects of time zones on these data types is covered specifically in 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.

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

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

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

The behaviour differs depending on at least three factors:

• 客户端中的时区设置.
• 值的数据类型(即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)

这篇关于PostgreSQL 中带/不带时区的时间戳之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！