在Postgres中的时区之间转换 [英] Converting Between Timezones in Postgres
问题描述
我正在尝试了解Postgre中的时间戳和时区。我想我明白了，直到我发表
由于我们给它一个 timestamptz 作为输入，它将输出一个 timestamp 。换句话说，它会将绝对时间点 2016-01-01 00：00Z 转换为美国/太平洋，即洛杉矶的时钟在该绝对时间点显示。
在示例2中，我们做相反的事情，即取 timestamp 并将其转换为 timestamptz 。换句话说，我们在问：洛杉矶的时钟显示 2016-01-01 00:00 的绝对时间是什么？
您提到：
好，
'2016-01-01 00时区功能的：00':: timestamp不再是字符串，而是实际的时间戳。在哪个时区？
'2016-01-01 00:00':: timestamp 是时间戳，即时间。它没有时区的概念。
我想您可能还没有完全理解 timestamp 之间的区别。和 timestamptz ，这是关键。只需将它们视为 wall time ，即挂在墙上的时钟在世界某个地方显示的时间，以及绝对时间，即我们宇宙中的绝对时间
您在自己的答案中所举的例子并不十分准确。
SELECT ts FROM（VALUES
（timestamptz'2012-03-05 17：00：00 + 0'）-输出2012-03-05 17：00：00 + 00 --1
，（timestamptz'2012-03-05 18：00：00 + 1'）-输出2012-03-05 17：00：00 + 00 --2
，（timestamp'2012-03-05 18：00：00 + 1'）-输出2012-03-05 18：00：00 + 00 --3
，（时间戳'2012-03-05 11:00:00'在时区' +6'）-输出2012-03-05 17：00：00 + 00 --4
（（时间戳'2012-03-05 17:00:00'在时区'UTC'）-输出2012-03-05 17：00：00 + 00 --5
，（时间戳'2012-03-05 17:00:00':: timestamp）-输出2012-03-05 17:00 ：00 + 00 --6
，（timestamp'2012-03-05 17:00:00':: timestamptz）-输出2012-03-05 17：00：00 + 00 --7
）t（ts）;
您的示例的问题在于，您正在用一个列构造一个数据集。由于列只能具有一种类型，因此即使某些值的计算方式为，每一行（在这种情况下，也就是单个值）都将转换为相同的类型，即 timestamptz 。 时间戳（例如值3）。因此，您在这里还有一个额外的隐式转换。
让我们将示例拆分为单独的查询，然后看看发生了什么：
示例1
db =＃SELECT timestamptz'2012-03-05 17： 00：00 + 0';
timestamptz
------------------------
2012-03-05 17：00：00 + 00
您可能已经知道， timestamptz'2012-03-05 17:00 ：00 + 0'和'2012-03-05 17：00：00 + 0':: timestamptz 是等效的（我更喜欢后者）。因此，仅使用与文章中相同的语法，我将重写：
db =＃SELECT'2012-03 -05 17：00：00 + 0':: timestamptz;
timestamptz
------------------------
2012-03-05 17：00：00 + 00
现在，这是怎么回事？好吧，少于您最初的解释。该字符串被简单地解析为 timestamptz 。打印结果后，它将使用当前设置的时区配置将其转换回人类可读的基础数据结构表示形式，即 2012- 03-05 17：00：00 + 00 。
让我们更改时区配置，看看会发生什么：
db =＃将时区设置为欧洲/柏林；
SET
db =＃选择‘2012-03-05 17：00：00 + 0’:: timestamptz;
timestamptz
------------------------
2012-03-05 18：00：00 + 01
唯一改变的是<$> how timestamptz 显示在屏幕上，即使用欧洲/柏林时区。
示例2
db =＃SELECT timestamptz'2012-03-05 18：00：00 + 1';
timestamptz
------------------------
2012-03-05 17：00：00 + 00
（1行）
再次，只是解析日期。
示例3
db =＃SELECT timestamp'2012-03 -05 18：00：00 + 1';
时间戳
---------------------
2012-03-05 18:00:00
（1行）
这与'2012-03-05 18相同： 00：00 + 1':: timestamp 。这里发生的是，时区偏移量只是被忽略了，因为您要的是时间戳。
示例4
db =＃SELECT时间戳'2012-03-05 11:00:00'AT TIME ZONE'+6';
时区
------------------------
2012-03-05 17：00：00 + 00
（1行）
让我们更简单地重写：
db =＃SELECT timezone（'+ 6'，'2012-03-05 11:00:00':: timestamp）;
时区
------------------------
2012-03-05 17：00：00 + 00
（1行）
这是在问：时钟的绝对时间是多少？时区的偏移量为+6小时的墙显示 2012-03-05 11:00:00 ？
示例5
db =＃SELECT时间戳'2012-03-05 17:00： 00'AT TIME ZONE'UTC';
时区
------------------------
2012-03-05 17：00：00 + 00
（1行）
我们重写：
db =＃SELECT timezone（'UTC'，'2012-03-05 17:00:00':: timestamp）;
时区
------------------------
2012-03-05 17：00：00 + 00
（1行）
这是在问：时钟的绝对时间是多少？ UTC时区的墙显示 2012-03-05 17:00:00 ？
示例6
db =＃SELECT时间戳'2012-03-05 17:00:00':: timestamp;
时间戳
---------------------
2012-03-05 17:00:00
（1行）
在这里，您要将两次投射到 timestamp ，这没有区别。让我们简化一下：
db =＃SELECT‘2012-03-05 17:00:00’:: timestamp;
时间戳
---------------------
2012-03-05 17:00:00
（1行）
我想这很清楚。
示例7
db =＃SELECT时间戳'2012-03-05 17:00:00 ':: timestamptz;
timestamptz
------------------------
2012-03-05 17：00：00 + 00
（1行）
我们重写：
db =＃SELECT（'2012-03-05 17:00:00':: timestamp）:: timestamptz;
timestamptz
------------------------
2012-03-05 17：00：00 + 00
（1行）
您首先将字符串解析为 timestamp ，然后使用当前设置的 timezone 将其转换为 timestamptz 。如果更改时区，则会得到其他信息，因为Postgres在转换 timestamp （或缺少字符串的字符串）时会假定该时区时区信息）到 timestamptz ：
db =＃将时区设置为'欧洲/柏林'；
SET
db =＃SELECT（'2012-03-05 17:00:00':: timestamp）:: timestamptz;
timestamptz
------------------------
2012-03-05 17：00：00 + 01
（1行）
此绝对时间用UTC表示， 2012-03-05 16：00：00 + 00 ，因此与原始示例不同。
我希望这可以弄清楚。同样，了解 timestamp 和 timestamptz 之间的区别是关键。想想壁挂时间与绝对时间。
I am trying to understand the timestamps and timezones in Postgre. I think I got it, until I red this article.
Focus on the "Converting Between Timezones" part. It has two examples.
(Consider the default timezone configuration to be UTC.)
Example 1
db=# SELECT timezone('US/Pacific', '2016-01-01 00:00'); outputs 2015-12-31 16:00:00
According to the article and what I understand, because the '2016-01-01 00:00' part of the timezone function is just a string, it is silently converted to the default UTC. So from '2016-01-01 00:00' UTC it is then converted to US/Pacific as asked by the timezone function, that is 2015-12-31 16:00:00.
Example 2
db=# SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamp); outputs 2016-01-01 08:00:00+00
Excuse me, I dont see why and the explanation there does not help. Ok, the '2016-01-01 00:00'::timestamp part of the timezone function is no longer a string, but an actual timestamp. In what timezone? If it is UTC, the output would have to be the same as the Example 1. So it is automatically converted to US/Pacific? Then the output is in UTC? But why? I asked for a US/Pacific in my timezone not a UTC.
Please explain how the timezone behaves when gets a timestamp and gets asked to transform it. Thank you.
Let me explain the two examples:
In both we assume a timezone UTC (i.e. SET timezone TO UTC).
db=# SELECT timezone('US/Pacific', '2016-01-01 00:00');
timezone
---------------------
2015-12-31 16:00:00
(1 row)
This is equivalent to SELECT timezone('US/Pacific', '2016-01-01 00:00'::timestamptz), i.e. Postgres implicitly converted the string to a timestamptz.
We know that the timezone function converts back and forth between timestamp and timestamptz:
Since we are giving it a timestamptz as input, it'll output a timestamp. In other words, it is converting the absolute point in time 2016-01-01 00:00Z to a wall time in US/Pacific, i.e. what the clock in Los Angeles showed at that absolute point in time.
In example 2 we are doing the opposite, namely taking a timestamp and converting it to a timestamptz. In other words, we are asking: what was the absolute point in time when the clock in Los Angeles showed 2016-01-01 00:00?
You mention:
Ok, the
'2016-01-01 00:00'::timestamppart of the timezone function is no longer a string, but an actual timestamp. In what timezone?
'2016-01-01 00:00'::timestamp is a timestamp, i.e. a wall time. It doesn't have a notion of timezone.
I think you might not have fully understood the difference between timestamp and timestamptz, which is key here. Just think of them as wall time, i.e. the time that showed somewhere in the world on a clock hanging on the wall, and absolute time, i.e. the absolute time in our universe.
The examples you make in your own answer are not quite accurate.
SELECT ts FROM (VALUES
(timestamptz '2012-03-05 17:00:00+0') -- outputs 2012-03-05 17:00:00+00 --1
,(timestamptz '2012-03-05 18:00:00+1') -- outputs 2012-03-05 17:00:00+00 --2
,(timestamp '2012-03-05 18:00:00+1') -- outputs 2012-03-05 18:00:00+00 --3
,(timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6') -- outputs 2012-03-05 17:00:00+00 --4
,(timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC') -- outputs 2012-03-05 17:00:00+00 --5
,(timestamp '2012-03-05 17:00:00'::timestamp) -- outputs 2012-03-05 17:00:00+00 --6
,(timestamp '2012-03-05 17:00:00'::timestamptz) -- outputs 2012-03-05 17:00:00+00 --7
) t(ts);
The problem with your example is that you're constructing one data set with a single column. Since a column can only have one type, each row (or single value in this case) is being converted to the same type, namely timestamptz, even though some values were calculated as timestamp (e.g. value 3). Thus, you have an additional implicit conversion here.
Let's split the example into separate queries and see what is going on:
Example 1
db=# SELECT timestamptz '2012-03-05 17:00:00+0';
timestamptz
------------------------
2012-03-05 17:00:00+00
As you might already know, timestamptz '2012-03-05 17:00:00+0' and '2012-03-05 17:00:00+0'::timestamptz are equivalent (I prefer the latter). Thus, just to use the same syntax as in the article, I'll rewrite:
db=# SELECT '2012-03-05 17:00:00+0'::timestamptz;
timestamptz
------------------------
2012-03-05 17:00:00+00
Now, what's going on here? Well, less than in your original explanation. The string is simply parsed as a timestamptz. When the result gets printed, it uses the currently set timezone config to convert it back to a human readable representation of the underlying data structure, i.e. 2012-03-05 17:00:00+00.
Let's change the timezone config and see what happens:
db=# SET timezone TO 'Europe/Berlin';
SET
db=# SELECT '2012-03-05 17:00:00+0'::timestamptz;
timestamptz
------------------------
2012-03-05 18:00:00+01
The only thing that changed is how the timestamptz gets printed on screen, namely using the Europe/Berlin timezone.
Example 2
db=# SELECT timestamptz '2012-03-05 18:00:00+1';
timestamptz
------------------------
2012-03-05 17:00:00+00
(1 row)
Again, just parsing the date.
Example 3
db=# SELECT timestamp '2012-03-05 18:00:00+1';
timestamp
---------------------
2012-03-05 18:00:00
(1 row)
This is the same as '2012-03-05 18:00:00+1'::timestamp. What happens here is that the timezone offset is simply ignored because you're asking for a timestamp.
Example 4
db=# SELECT timestamp '2012-03-05 11:00:00' AT TIME ZONE '+6';
timezone
------------------------
2012-03-05 17:00:00+00
(1 row)
Let's rewrite to be simpler:
db=# SELECT timezone('+6', '2012-03-05 11:00:00'::timestamp);
timezone
------------------------
2012-03-05 17:00:00+00
(1 row)
This is asking: what was the absolute time when the clock on the wall in the timezone with an offset of +6 hours was showing 2012-03-05 11:00:00?
Example 5
db=# SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC';
timezone
------------------------
2012-03-05 17:00:00+00
(1 row)
Let's rewrite:
db=# SELECT timezone('UTC', '2012-03-05 17:00:00'::timestamp);
timezone
------------------------
2012-03-05 17:00:00+00
(1 row)
This is asking: what was the absolute time when the clock on the wall in the timezone UTC was showing 2012-03-05 17:00:00?
Example 6
db=# SELECT timestamp '2012-03-05 17:00:00'::timestamp;
timestamp
---------------------
2012-03-05 17:00:00
(1 row)
Here you're casting twice to timestamp, which makes no difference. Let's simplify:
db=# SELECT '2012-03-05 17:00:00'::timestamp;
timestamp
---------------------
2012-03-05 17:00:00
(1 row)
That's clear I think.
Example 7
db=# SELECT timestamp '2012-03-05 17:00:00'::timestamptz;
timestamptz
------------------------
2012-03-05 17:00:00+00
(1 row)
Let's rewrite:
db=# SELECT ('2012-03-05 17:00:00'::timestamp)::timestamptz;
timestamptz
------------------------
2012-03-05 17:00:00+00
(1 row)
You're first parsing the string as a timestamp and then converting it to a timestamptz using the currently set timezone. If we change the timezone, we get something else because Postgres assumes that timezone when converting a timestamp (or a string lacking timezone information) to timestamptz:
db=# SET timezone TO 'Europe/Berlin';
SET
db=# SELECT ('2012-03-05 17:00:00'::timestamp)::timestamptz;
timestamptz
------------------------
2012-03-05 17:00:00+01
(1 row)
This absolute time, expressed in UTC, is 2012-03-05 16:00:00+00, thus different from the original example.
I hope this clarifies things. Again, understanding the difference between timestamp and timestamptz is key. Think of wall time versus absolute time.
这篇关于在Postgres中的时区之间转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
