I try to understand some query examples using TIMESTAMP WITH TIMEZONE in Postgres.

我试着理解一些在Postgres中使用TIMESTAMP WITH TIMEZONE的查询示例。

Given are some queries that convert the now-result into TimeStamp for timezones "CET" and "CEST". "CEST" is 1 hour ahead of CET (summer time)

给出了一些将NOW-结果转换为时区“CET”和“CEST”的时间戳的查询。“CEST”比CET(夏季时间)早1小时。

Examples:

例如：

SELECT 1, now()
UNION
SELECT 2, now()::timestamp
UNION
SELECT 3, now() AT TIME ZONE 'CET'
UNION
SELECT 4, now()::timestamp AT TIME ZONE 'CET'
UNION
SELECT 5, now() AT TIME ZONE 'CEST'
UNION
SELECT 6, now()::timestamp AT TIME ZONE 'CEST'
;

Results:

结果：

    1, 2023-09-10 17:07:10.524389 +00:00
    2, 2023-09-10 17:07:10.524389 +00:00
    3, 2023-09-10 18:07:10.524389 +00:00
    4, 2023-09-10 16:07:10.524389 +00:00
    5, 2023-09-10 19:07:10.524389 +00:00
    6, 2023-09-10 15:07:10.524389 +00:00

So, the difference between

所以，两者之间的区别

SELECT 5, now() AT TIME ZONE 'CEST'

… and:

…以及：

SELECT 6, now()::timestamp AT TIME ZONE 'CEST'

… is 4 hours. Why?

…是4个小时。为什么？

The difference between

两者之间的区别

SELECT 3, now() AT TIME ZONE 'CET'

… and:

…以及：

SELECT 4, now()::timestamp AT TIME ZONE 'CET'

… is 2 hours. I don't understand that either.

…是2个小时。我也不明白这一点。

The current timezone of my Postgres-Container is UTC:

我的Postgres-Container的当前时区是UTC：

SELECT current_setting('TIMEZONE');

> UTC

Can someone explain please?

有人能解释一下吗？

优秀答案推荐

tl;dr

• Avoid UNION here. Your mix of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE types results in implicit casting from the second to the first because of UNION.


• You are using the wrong type, TIMESTAMP WITHOUT TIME ZONE, where you should be using only TIMESTAMP WITH TIME ZONE.


• Invoking AT TIME ZONE flips back and forth between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE types.


• You are using pseudo time zones (CET/CEST) that only indicate whether Daylight Saving Time is in effect or not. Any of several real time zones could be intended by those pseudo-zones.


• The psql console app is unfortunately applying a default time zone to TIMESTAMP WITH TIME ZONE values that are actually always in UTC (have an offset of zero).

Wrong type: TIMESTAMP WITHOUT TIME ZONE

You are using the wrong data type. TIMESTAMP is an abbreviation for TIMESTAMP WITHOUT TIME ZONE per the SQL standard.

您使用了错误的数据类型。TIMESTAMP是SQL标准中TIMESTAMP WITHOUT TIME ZONE的缩写。

That type has no concept of time zone or offset-from-UTC. So it cannot represent a moment, a specific point on the timeline. That type represents merely an ambiguous date with time-of-day, nothing more. If you tell me "noon on January 23, 2024", how do I know if you mean noon in Tokyo, noon in Toulouse, or noon in Toledo Ohio — three very different moments, several hours apart.

该类型没有时区或与UTC的偏移量概念。因此，它不能代表时间线上的某个时刻，某个特定的时间点。该类型仅表示带有时间的模棱两可的日期，仅此而已。如果你告诉我“2024年1月23日中午”，我怎么知道你指的是东京的中午，图卢兹的中午，还是俄亥俄州托莱多的中午--这是三个截然不同的时刻，相隔几个小时。

See the Postgres documentation.

请参阅Postgres文档。

Correct type: TIMESTAMP WITH TIME ZONE

You should be using the other type, 👉🏽 TIMESTAMP WITH TIME ZONE. This type does have the concept of offset, always storing a submitted value in UTC (an offset of zero hours-minutes-seconds from the temporal meridian of UTC).

您应该使用另一种类型，带有时区的👉🏽时间戳。此类型确实具有偏移量的概念，始终以UTC存储提交的值(与UTC的时间子午线的偏移量为零小时-分钟-秒)。

Any offset or zone information accompanying a submitted value is used to adjust to UTC and then discarded.

提交的值附带的任何偏移量或区域信息都用于调整到UTC，然后丢弃。

If you want to retain information about that original zone, you’ll need to record that as text in a second column.

如果您希望保留有关原始区域信息，则需要将其作为文本记录在第二列中。

You may abbreviate TIMESTAMP WITH TIME ZONE as TIMESTAMPTZ, as a Postgres-specific feature. Personally, I always spell out both types with their full name to avoid confusion from misreading.

您可以将带有时区的时间戳缩写为TIMESTAMPTZ，作为Postgres特有的功能。就我个人而言，我总是用他们的全名拼写这两种类型，以避免误读造成混淆。

Be aware that this store-in-UTC behavior is the policy choice of the Postgres team. Some other database engines work the same way; others do not. Always study the documentation. The SQL standard is woefully lacking in specifying behavior of date-time handling.

请注意，这种在UTC中存储的行为是Postgres团队的策略选择。其他一些数据库引擎的工作方式与此相同，而其他数据库引擎则不然。一定要仔细阅读文档。令人遗憾的是，SQL标准在指定日期-时间处理行为方面缺乏。

Beware of tools

Unfortunately, some database access tools and middleware may choose to ⚠️ inject some default time zone onto the UTC value retrieved from Postgres. While well-intentioned, this anti-feature creates the false illusion of that zone having been stored. The pgAdmin app is one such tool.

不幸的是，一些数据库访问工具和中间件可能会选择将一些默认时区添加到从Postgres检索的UTC值中。️虽然意图良好，但此反功能会造成该区域已被存储的错误错觉。pgAdmin应用程序就是这样一个工具。

I would argue that a data access tool should always “tell the truth”, and not fiddle with retrieval results. But those tool creators did not ask me 😞 .

我认为，数据访问工具应该始终“说实话”，而不是摆弄检索结果。但那些工具创建者并没有问我😞。

So understand that in Postgres: Any and all TIMESTAMP WITH TIME ZONE values were stored with an offset of zero, and were retrieved with an offset of zero. Any other zone or offset you see was overlaid by a meddlesome tool.

因此，请理解在Postgres中：任何和所有带有时区值的时间戳都是以零偏移量存储的，并且是以零偏移量检索的。你看到的任何其他区域或偏移量都被一个好管闲事的工具覆盖了。

Real time zones

CET and CEST are not real time zones.

CET和CEST不是实时时区。

Such pseudo-zones are to be used only in presentation to the user, never in data storage or data exchange.

这样的伪区只能用于向用户呈现，而不能用于数据存储或数据交换。

Real time zones have a name in the format of Continent/Region. A few examples:

实时时区的名称格式为大陆/地区。下面是几个例子：

• Europe/Paris


• Europe/Berlin


• Europe/Stockholm


• Europe/Warsaw

Your examples

Let's look at your sample code.

让我们来看一下您的示例代码。

SELECT 1, now()
UNION
SELECT 2, now()::timestamp
UNION
SELECT 3, now() AT TIME ZONE 'CET'
UNION
SELECT 4, now()::timestamp AT TIME ZONE 'CET'
UNION
SELECT 5, now() AT TIME ZONE 'CEST'
UNION
SELECT 6, now()::timestamp AT TIME ZONE 'CEST'
;

Per the documentation for Postres 15.4 date-time functions and date-time types…

根据Postres15.4日期-时间函数和日期-时间类型…的文档

The first one, now() returns a timestamp with time zone value. We can try it by setting the session to an offset of zero, to UTC.

第一个函数now()返回一个带有时区值的时间戳。我们可以通过将会话的偏移量设置为零来尝试它。

set timezone='UTC' ;

Verify with:

通过以下方式进行验证：

show timezone ;

Call the now function.

调用Now函数。

             now              
------------------------------
 2023-09-11 01:13:03.64721+00
(1 row)

We see an offset of +00, meaning an offset of zero hours-minutes-seconds from UTC. So this is the time-of-day seen on the Shepherd Gate Clock at the Royal Observatory, Greenwich (well, within a second or so, we'll not get into GMT versus UTC here, irrelevant for practical business contexts).

我们看到偏移量为+00，这意味着与UTC的偏移量为零小时-分-秒。这是格林威治皇家天文台牧羊人门时钟上显示的一天中的时间(好的，在大约一秒钟内，我们不会进入GMT与UTC的对比，这与实际的商业环境无关)。

Set the default time zone of our session to Berlin time.

将我们会议的默认时区设置为柏林时间。

set timezone='Europe/Berlin' ;

And SELECT now() ;.

并选择立即()；。

2023-09-11 03:19:46.171143+02

We see the hour is two hours ahead, 03 versus the 01 seen above. This accords with the +02. These two results represent the same moment, the same point on the timeline (or would have, if I'd typed faster). Their wall-clock time differs, as it would for someone in Iceland phoning someone in Berlin, and both look up simultaneously at their respective clock on the wall — one person sees 1 AM, the other person sees 3 AM.

我们看到小时提前了两个小时，03小时与上面看到的01小时相比。这与+02一致。这两个结果代表了同一时刻，同一时间轴上的同一时间点(如果我打字快一点的话，也会是这样)。他们的挂钟时间不同，就像冰岛的人给柏林的人打电话时，两人同时抬头看各自的挂钟--一个人看到凌晨1点，另一个人看到凌晨3点

BEWARE: We now see that the psql has the anti-feature of applying a default time zone while generating the text to display on the console. The value in Postgres, the TIMESTAMP WITHOUT TIME ZONE, is always in UTC. So we cannot exactly trust the output of psql in the sense that what the tool shows us is not exactly what the database engine produces.

注意：我们现在看到，在生成要在控制台上显示的文本时，psql具有应用默认时区的反功能。Postgres中的值(不带时区的时间戳)始终以UTC为单位。因此，我们不能完全信任psql的输出，因为该工具向我们显示的内容并不完全是数据库引擎生成的内容。

In contrast, if you retrieve this value in Java via JDBC with a call like myResultSet.getObject( … , OffsetDateTime.class ), you will always get a value with an offset of zero.

相反，如果您通过调用myResultSet.getObject(…)通过JDBC在Java中检索此值，OffsetDateTime.class)，则将始终获得偏移量为零的值。

Your next line:

你的下一句话是：

SELECT 2, now()::timestamp 

… used the wrong type, TIMESTAMP WITHOUT TIME ZONE. You are discarding valuable information, the offset-from-UTC with nothing gained in return. That line should have been:

…使用了错误的类型，没有时区的时间戳。你正在丢弃有价值的信息，即与UTC的偏移量，而没有任何回报。这句话应该是：

SELECT 2, now()::TIMESTAMP WITH TIME ZONE  

But this is silly, as you already had a TIMESTAMP WITHOUT TIME ZONE value in hand. The cast is meaningless, adding no value.

但这很愚蠢，因为您已经有了一个没有时区值的时间戳。演员阵容毫无意义，没有任何价值。

Your third line:

你的第三行：

SELECT 3, now() AT TIME ZONE 'CET'

… uses a pseudo time zone. That should have used a real time zone name. Something like this:

…使用伪时区。应该用一个真实的时区名称。大概是这样的：

SELECT 3, now() AT TIME ZONE 'Europe/Berlin'

But you have to be careful with the AT TIME ZONE operator. The operator flips the type of the input back and forth between timestamp with time zone and timestamp without time zone types:

但你必须小心使用AT时区接线员。操作员在带时区的时间戳和不带时区类型的时间戳之间来回切换输入的类型：

• If you operate on a timestamp without time zone value, you get back a timestamp with time zone value.


• And vice versa, operating on a timestamp with time zone returns a timestamp without time zone.

While adding or stripping the offset, an adjustment is made to the time & date per the time zone you specify.

在添加或剥离偏移时，会根据您指定的时区对时间和日期进行调整。

So this line:

所以这一行：

SELECT now() AT TIME ZONE 'Europe/Berlin' ;

… gives you the date & time as seen in the Berlin region, without regard to the default time zone of our database session.

…为您提供在柏林地区看到的日期和时间，而不考虑我们数据库会话的默认时区。

So in our example scenario of 1 AM UTC, the line above will always return the text below with a 3 AM time-of-day, regardless of whether the default time zone is UTC, Europe/Berlin, or Asia/Tokyo.

因此，在我们的UTC为凌晨1点的示例方案中，无论默认时区是UTC、欧洲/柏林还是亚洲/东京，上面的行将始终返回以下文本和凌晨3点的时间。

         timezone          
---------------------------
 2023-09-11 03:39:19.67042
(1 row)

But notice in that line above that we have lost our offset indication. That is because we called AT TIME ZONE to operate on a timestamp with time zone, and we got back a timestamp without time zone, having lost our offset content after making the date-time adjustment.

但请注意，在上面的那一行中，我们已经失去了补偿指示。这是因为我们调用AT时区来操作一个带有时区的时间戳，我们得到了一个没有时区的时间戳，在进行日期-时间调整后丢失了我们的偏移量内容。

Your fourth line, SELECT 4, now()::timestamp AT TIME ZONE 'CET' repeats two of the mistakes of your earlier lines: (a) casting a timestamp with time zone into a timestamp without time zone thereby losing the offset info, and (b) using a pseudo time zone. So we can delete this line, as it does not further our understanding.

您的第四行，SELECT 4，NOW()：：TIMESTAMP AT TIMEZONE‘CET’重复了前面几行中的两个错误：(A)将带有时区的时间戳转换为不带时区的时间戳，从而丢失了偏移信息，以及(B)使用伪时区。所以我们可以删除这一行，因为它不会加深我们的理解。

Your fifth line, SELECT 5, now() AT TIME ZONE 'CEST', uses a different pseudo time zone, CEST versus CET. The difference between these is that the S means to indicate "Summer Time", that is, that Daylight Saving Time (DST) is being observed. This is one of the problems with these pseudo-zones: people often confuse the DST versus non-DST counterparts and use the wrong one. Again, we should not be using these pseudo-zones in our programming. So we can drop this line of yours.

您的第五行，SELECT 5，NOW()，在时区‘CEST’，使用不同的伪时区，CEST和CET。两者的不同之处在于，S的意思是表示“夏令时”，即正在遵守夏令时(DST)。这是这些伪区的问题之一：人们经常混淆DST和非DST对应的区域，并使用错误的DST。同样，我们不应该在编程中使用这些伪区域。这样我们就可以放弃你的这一行了。

Your sixth line, SELECT 6, now()::timestamp AT TIME ZONE 'CEST', can also be ignored. Again, this line makes an inappropriate cast, and inappropriately uses pseudo-zones.

您的第六行SELECT 6，NOW()：：TIMESTAMP AT时区‘CEST’也可以忽略。同样，这一行的演员阵容不恰当，并且不适当地使用了伪区。

By using real time zone names, such as Europe/Berlin, rather than pseudo-zones such as CET/CEST, we let the software determine if DST is in effect at our given moment. Postgres has its own embedded copy of tzdata. As long as that file is up-to-date, then we can rely on Postgres to determine DST or not for a particular date & time in Europe/Berlin.

通过使用实时时区名称（如欧洲/柏林），而不是伪时区（如CET/CEST），我们让软件确定DST在我们给定的时刻是否有效。Postgres有自己的嵌入式tzdata副本。只要该文件是最新的，那么我们就可以依靠Postgres来确定欧洲/柏林特定日期和时间的夏令时。

So we can boil down your example code to a modified version of the only useful/sensible lines in your example code, the first and third lines:

因此，我们可以将您的示例代码归结为示例代码中唯一有用/合理的行的修改版本，即第一行和第三行：

SET timezone='UTC' 
;
SELECT 1 AS RowNo , now() 
;
SELECT 3 as RowNo, now() AT TIME ZONE 'Europe/Berlin' 
;

Result:

结果：

SET
 rowno |              now              
-------+-------------------------------
     1 | 2023-09-11 02:05:53.188829+00
(1 row)

 rowno |          timezone          
-------+----------------------------
     3 | 2023-09-11 04:05:53.189836
(1 row)

Implicit casting of mixed types produced by UNION

We another issue to consider, raised in a Comment by jjanes. 👉🏾 Your use of UNION to combine the SELECT queries alters the results.

我们是另一个需要考虑的问题，这是jjanes在一条评论中提出的。使用👉🏾组合SELECT查询会改变结果。

Notice how, in the results shown directly above, # 3 has no offset indicator. This means the result is a TIMESTAMP WITHOUT TIME ZONE value. But # 1 does have an offset indicator, which means that item is a TIMESTAMP WITH TIME ZONE value. So we have two different separate SELECT statements whose results are of different data types.

请注意，在上面直接显示的结果中，#3没有偏移量指示器。这意味着结果是不带时区值的时间戳。但是#1确实有一个偏移量指示符，这意味着该项目是一个带有时区值的时间戳。因此，我们有两个不同的单独SELECT语句，它们的结果属于不同的数据类型。

Let's try the approach seen in the Question with UNION. We replace the semicolon with a UNION.

让我们尝试一下问题中关于Union的方法。我们将分号替换为UNION。

SET timezone='UTC' 
;
SELECT 1 AS RowNo , now() 
UNION
SELECT 3 as RowNo, now() AT TIME ZONE 'Europe/Berlin' 
;

Whoa! These results do not match the results above. Here we see an offset indicator on both # 1 and # 3. So now # 3 is a TIMESTAMP WITH TIME ZONE value. Why? The type of a column must be the same across rows. How? Postgres implicitly cast the TIMESTAMP WITHOUT TIME ZONE value into a TIMESTAMP WITH TIME ZONE value, applying an offset where we had none in the previous example.

哇！这些结果与上述结果不匹配。在这里我们可以看到#1和#3上都有一个偏移指示器.所以现在#3是一个TIMESTAMP WITH TIME ZONE值。为什么？列的类型在各行中必须相同。怎么做？Postgres隐式地将TIMESTAMP WITHOUT TIME ZONE的值转换为TIMESTAMP WITH TIME ZONE的值，并在上一个示例中没有偏移量的地方应用偏移量。

SET
 rowno |              now              
-------+-------------------------------
     1 | 2023-09-11 02:10:13.934363+00
     3 | 2023-09-11 04:10:13.934363+00
(2 rows)

The key to understand here is that the date-time functions return date-time values, not mere strings. (The psql app then generates text from these typed values for display.)

这里要理解的关键是日期-时间函数返回的是日期-时间值，而不仅仅是字符串。(The psql应用程序然后从这些类型的值中生成文本以供显示。

To fix the mixing of types problem, Postgres tries to be helpful by casting. So, in the end, the UNION version of this code produces semantically different results than the non-UNION version.

为了解决混合类型的问题，postgres试图通过强制转换来提供帮助。因此，最终，该代码的UNION版本产生的结果在语义上与非UNION版本不同。

This means the Question's code example is even less useful for trying to learn about Postgres' date-time handling. For our studies, we must keep those example statements separate, without UNION.

这意味着该问题的代码示例对于尝试了解Postgres的日期-时间处理更没有用处。对于我们的学习，我们必须将这些示例语句分开，而不是UNION。

---

By the way, time zone versus offset-from-UTC:

顺便说一句，时区与UTC的偏移量：

• An offset is merely a number of hours-minutes-seconds ahead of, or behind, the temporal meridian of UTC.


• A time zone is much more. A time zone is a named history of the past, present, and future changes to the offset used by the people of a particular region as decided by their politicians.