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).
• 是否用特定时区指定值.

• The timezone setting in the client.
• The data type (i.e. WITH TIME ZONE or WITHOUT TIME ZONE) of the value.
• Whether the value is specified with a particular 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屋！