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

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

时区对这些数据类型的影响在文档中是覆盖的具体。差异来自于系统可以合理地知道的值:

• 将时区作为值的一部分，该值可以在客户端中作为本地时间呈现。

• 如果没有时区作为值的一部分，显然默认时区是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)

下面是一个应该会有帮助的例子。如果您有一个带有时区的时间戳，您可以将该时间戳转换为任何其他时区。如果你没有一个基本时区，它将不会被正确转换。

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

我试图比参考的PostgreSQL文档更容易理解地解释它。

TIMESTAMP变量都没有存储时区(或偏移量)，尽管它们的名称表明。区别在于对存储数据的解释(以及预期的应用程序)，而不是存储格式本身:

• TIMESTAMP WITHOUT TIME ZONE存储当地的 date-time (aka。挂历日期和挂钟时间)。就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时区呈现的TIMESTAMP WITH TIME ZONE给出的年、月、日、小时、分、秒和微秒与本地日期-时间中的相同。但它并不代表UTC解释中所说的时间线上的点，它只是本地日期-时间字段编码的方式。(这是时间线上的一组点，因为实际时区不是UTC;我们不知道它是什么。)

时间戳vs时间戳

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

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

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

引用文件中的内容:

对于带时区的时间戳，内部存储的值总是in UTC(国际协调时间，传统上称为格林威治标准时间) 时间,GMT)。具有显式指定时区的输入值为 使用该时区的适当偏移量转换为UTC。如果 输入字符串中没有指定时区，则假定为 在系统的TimeZone参数所显示的时区中，为 使用时区的偏移量转换为UTC。给… 简单的类比，时间戳值表示时间中的一个瞬间，即 所有观看的人都能同步观看。但是时间戳值 表示时钟的特定方向，该方向将表示

.

.

对于几乎所有用例，时间戳几乎总是一个更好的选择。由于timestamptz和timestamp占用相同的8字节数据，这种选择变得更加容易。

执行以下命令，查看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中的时间戳精度环境，希望我的完整答案和解决方案将帮助你。

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

概括地说，TIMESTAMP WITHOUT TIME ZONE不保存任何与时区相关的信息，如果你给date time和timezone信息，它会占用date &只关注时间，忽略时区

例如

当我将这个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

注意:UTC + 5.30是IST

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

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