当前位置：文江博客话题详情

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

发布于 2024-11-04 21:18:14 字数 122 浏览 2 评论 0 原文

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

真心难拥有 2024-11-11 21:18:15

Timestamptz 与 Timestamp

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

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

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

引用文档：

对于带时区的时间戳，内部存储的值始终位于
UTC（通用协调时间，传统上称为格林威治标准时间）
时间，格林威治标准时间）。指定了显式时区的输入值是
使用该时区的适当偏移量转换为 UTC。如果
输入字符串中没有指定时区，则假定为
在系统的TimeZone参数指示的时区中，并且是
使用时区的偏移量转换为 UTC。给一个
简单的类比，timestamptz 值代表时间上的一个瞬间，
对于任何观看它的人来说都是同一时刻。但时间戳值只是
代表时钟的特定方向，这将代表
基于您的时区的不同时间实例。

对于几乎任何用例，timestamptz 几乎总是更好的选择。由于 timestamptz 和 timestamp 都占用相同的 8 字节数据，这一选择变得更加容易。

来源：
https://hasura.io/blog/postgres-日期时间数据类型-graphql-fd926e86ee87/

回复收藏 0 原文

自找没趣 2024-11-11 21:18:15

差异显示在 PostgreSQL 官方文档中。请参阅文档进行深入挖掘。

简而言之，如果您提供带有时区信息的日期时间，则 TIMESTAMP WITHOUT TIME ZONE 不会保存任何时区相关信息，它需要日期和时间。仅时间并忽略时区

例如

当我将此12:13, 11 June 2021 IST保存到PostgreSQL时TIMESTAMP NOT 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 is IST

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

因此 PostgreSQL 中推荐的时间戳类型是 TIMESTAMP WITH时区或TIMESTAMPZ

回复收藏 0 原文

桃扇骨 2024-11-11 21:18:15

运行以下命令来查看 pgAdmin 中的差异：

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 环境中遇到与我类似的时间戳精度问题，希望我的完整的答案和解决方案将为您提供帮助。

Run the following to see diff in pgAdmin:

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;

If you have similar issues I had of timestamp precision in Angular / Typescript / Node API / PostgreSql environment, hope my complete answer and solution will help you out.

回复收藏 0 原文

把回忆走一遍 2024-11-11 21:18:15

我喜欢将其视为没有时区的时间戳就像存储特定的时钟指针设置并在检索时将它们复制粘贴到其他时钟上。

假设没有时区的时间戳，您拍摄某个时钟的快照并存储它。此设置完全不知道它所在的时区，它只是一个没有时区上下文的值。然而，如果你得到它的纪元值，它将被解释为这个精确值在你的本地时区，这真的很令人困惑。例如，如果 3 个单独的数据库客户端将请求来自三个不同时区位置的时间戳 01-01-2025 00:00：UTC-8、UTC 和 UTC+8，则它们都将获得相同的日期他们的当地时间：

00:00 -8
00:00 +0
00:00 +8

但它代表 UTC 中的三个不同时间戳：

08:00 UTC
00:00 UTC
16:00 UTC

时间戳与时区的工作方式完全不同：它将在存储时间时对齐其偏移量以匹配 UTC。之后，获取此时间戳的客户端将根据其时区调整日期。当存储 01-01-2025 00:00 +3 时，它将存储为 31-12-2024 21:00 UTC，现在，请求此时间戳的相同客户端能够以 UTC 模式检索此时间戳，然后所有三个都将获得相同的标准化日期：

31-12-2024 21:00 UTC

或者，当尊重客户端会话时区时：

31-12-2024 13:00 -8
31-12-2024 21:00 UTC
01-01-2025 05:00 UTC

这是没有时区的时间戳的图形解释

回复收藏 0 原文

唠甜嗑 2024-11-11 21:18:14

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

时区对这些数据类型的影响专门介绍了< /a> 在文档中。差异在于系统可以合理地了解该值的内容：

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

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.
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:

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)

回复收藏 0 原文

天生の放荡 2024-11-11 21:18:14

我尝试比 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 时间线上的点。它看起来如何（多少小时、分钟等）取决于您所在的时区，但它总是指相同的“物理”时刻（如实际物理事件的时刻）。这
输入在内部转换为 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；我们不知道它是什么。）

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

Neither TIMESTAMP variants 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 WITHOUT TIME ZONE stores local date-time (aka. wall calendar date and wall clock time). Its time zone is unspecified as far as PostgreSQL can tell (though your application may know 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 ZONE stores 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.Date or java.sql.Timestamp of course).

Some say that both TIMESTAMP variations store UTC date-time. Kind of, but it's confusing to put it that way in my opinion. TIMESTAMP WITHOUT TIME ZONE is 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.)

回复收藏 0 原文

妥活 2024-11-11 21:18:14

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

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

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

回复收藏 0 原文

~没有更多了~