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

发布于05月04日

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

推荐答案

差异被覆盖在the PostgreSQL documentation for date/time types.是的，TIME或TIMESTAMP的治疗在WITH TIME ZONE或WITHOUT TIME ZONE之间有所不同.它不会影响值的存储方式；它会影响人们对它们的解读.

在文档中，时区对这些数据类型的影响为covered specifically.差异源于系统对价值的合理了解:

当时区作为值的一部分时，该值可以在客户端中呈现为本地时间.
如果没有时区作为该值的一部分，那么明显的默认时区是UTC，因此会针对该时区进行渲染.

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

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

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

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)