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

当数据类型是WITH TIME ZONE与WITHOUT TIME ZONE时，时间戳值在PostgreSQL中存储不同吗?这些区别可以用简单的测试用例来说明吗?

日期/时间类型的差异在PostgreSQL文档中有详细说明。是的，一个WITH TIME ZONE或WITHOUT TIME ZONE对TIME或TIMESTAMP的处理是不同的。它不会影响值的存储方式;它会影响它们的解释方式。

时区对这些数据类型的影响在文档中有详细介绍。差异来自于系统可以合理地知道的值:

使用时区作为值的一部分，该值可以在客户端呈现为本地时间
如果没有时区作为值的一部分，显然默认的时区是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)

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

无论名称如何，TIMESTAMP变体都不存储时区(或偏移量)。不同之处在于存储数据的解释(以及预期的应用程序)，而不是存储格式本身:

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时间线上的一个点。它看起来如何(多少小时，多少分钟，等等)取决于你的时区，但它总是指相同的"物理"瞬间(比如实际物理事件的时刻)。的input在内部转换为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)。

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

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

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

输出:

-[ 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

时间戳与时间戳

Postgres中的timestamptz字段基本上只是时间戳字段，Postgres实际上只是存储"规范化"的UTC时间，即使输入字符串中给出的时间戳具有时区。

如果你的输入字符串是:2018-08-28T12:30:00+05:30，当这个时间戳存储在数据库中时，它将被存储为2018-08-28T07:00:00。

与简单的时间戳字段相比，这样做的优点是，您输入到数据库的内容将与时区无关，并且当来自不同时区的应用程序插入时间戳时，或者当您将数据库服务器位置移动到不同的时区时，不会不准确。

从文档中引用:

对于带时区的时间戳，内部存储的值总是inUTC(世界协调时间，传统上称为格林尼治标准)时间,GMT)。指定了显式时区的输入值为使用该时区的适当偏移量转换为UTC。如果没有在输入字符串中说明时区，那么假定它是在系统的TimeZone参数指定的时区内，为使用时区的偏移量转换为UTC。给予…简单的类比，时间戳值表示时间中的一个瞬间，即对任何观看它的人来说都是一样的。但是时间戳值只是表示时钟的特定方向，它将表示根据您的时区设置不同的时间实例。

对于几乎任何用例，时间戳几乎总是更好的选择。timestamptz和timestamp占用相同的8字节数据，这使得选择更容易。

来源:https://hasura.io/blog/postgres-date-time-data-types-on-graphql-fd926e86ee87/

差异见PostgreSQL官方文档。请参考文档进行深入研究。

简而言之，TIMESTAMP WITHOUT TIME ZONE不保存任何与时区相关的信息，如果你给日期时间与时区信息，它需要日期&只有时间，忽略时区

例如

当我将12:13, 11 June 2021 IST保存到PostgreSQL时，TIMESTAMP WITHOUT TIME ZONE将拒绝时区信息并保存日期时间12:13,11 June 2021

但在TIMESTAMP WITH TIME ZONE的情况下，它将时区信息保存为UTC格式。

例如

当我将这个12:13, 11 June 2021 IST保存到PostgreSQL TIMESTAMP WITH TIME ZONE类型变量时，它将这次解释为UTC值和6:43,11 June 2021 UTC

NB: UTC + 5.30是IST

在时间转换期间，TIMESTAMP WITH TIME ZONE返回的时间将以UTC格式存储，我们可以将其转换为所需的时区，如IST或PST等。

所以PostgreSQL推荐的时间戳类型是TIMESTAMP WITH TIME ZONE或TIMESTAMPZ

运行以下命令查看pgAdmin中的diff:

create table public.testts (tz timestamp with time zone, tnz timestamp without time zone);
insert into public.testts values(now(), now());
select * from public.testts;

如果你有类似的问题，我有时间戳精度在Angular/Typescript/Node API/PostgreSql环境，希望我完整的答案和解决方案将帮助你。

相关内容

最新更新

热门标签：