在时区：“转换”？ [英] AT TIME ZONE: "convert"?
问题描述
Postgres手册说：
AT TIME ZONE结构允许将时间戳转换为
不同的时区。
我想大多数人都会认为这里的含义是什么，比如
" unit conversion"，而且时间戳值保持不变（很多
就像2英尺在被转换时变成24英寸。但是：
＃现在选择（）=现在（）时间区域''UTC'';
？列？
----------
f
（1排）
-
dave
---------------------------（播出结束） - -------------------------
提示6：您是否搜索了我们的列表档案？
http://archives.postgresql.org
The Postgres manual says:
The AT TIME ZONE construct allows conversions of time stamps to
different time zones.
I''d guess most people would think what''s meant here is something like
"unit conversion", and that the timestamp value stays the same (much
like 2 feet becomes 24 inches when it''s being "converted"). But:
# SELECT NOW() = NOW() AT TIME ZONE ''UTC'';
?column?
----------
f
(1 row)
--
dave
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
推荐答案
你误解了TIMESTAMP WITH TIMEZONE类型，它没有存储你给它的时区
，它只是一个点及时。在TIMEZONE上说
只需将它转换为TIMESTAMP而不用TIMEZONE当地时间
在您给出的时区内。所以你在宣传不同的东西。请参阅：
＃选择now（），now（）在时区''UTC'';
now |时区
------------------------------- + --------- -------------------
2004-11-01 14：07：24.563239 + 01 | 2004-11-01 13：07：24.563239
（1排）
他们有不同的时间，一个有时区，另一个没有。
比较这些可能会将您的本地时区添加到
右边的那个，这意味着结果是错误的。
＃ select（now（）在时区''UTC''）:: timestamptz;
timezone
--------------- ----------------
2004-11-01 13：10：01.136295 + 01
（1行）
希望这能为你澄清...
周一，2004年11月1日下午08:02:33 +0700，David Garamond写道：
You misunderstand the TIMESTAMP WITH TIMEZONE type, it doesn''t store
the timezone you gave it, it''s just a point in time. Saying AT TIMEZONE
just converts it to a TIMESTAMP WITHOUT TIMEZONE with the local time it
was in the timezone you gave it. So you are complaring different
things. See:
# select now(), now() at time zone ''UTC'';
now | timezone
-------------------------------+----------------------------
2004-11-01 14:07:24.563239+01 | 2004-11-01 13:07:24.563239
(1 row)
They have different hours and one has a timezone and the other doesn''t.
Comparing these probably adds your local timezone to the one on the
right which means the result is false.
# select (now() at time zone ''UTC'')::timestamptz;
timezone
-------------------------------
2004-11-01 13:10:01.136295+01
(1 row)
Hope this clarifies it for you...
On Mon, Nov 01, 2004 at 08:02:33PM +0700, David Garamond wrote:
抱歉，点击发送太早......
David Garamond写道：
Sorry, hit Sent too early...
David Garamond wrote:
Postgres手册说：
AT TIME ZONE构造允许将时间戳转换为不同的时区。
我猜大多数人都会认为这里的含义是什么类似
单位转换，a并且时间戳值保持不变（当它被转换时，很多像2英尺变成24英寸）。但是：
#NELING NOW（）=现在（）在时区''UTC'';
？栏？
----------
f
（1排）
比较：
＃select timestamptz''2004-11-01 12：00：00-05''=
timestamptz''2004-11-01 17：00：00-00'';
？专栏？
----------
t
（ 1行）
问题是：AT TIME TIME ZONE已经做了它原本应该做的事情吗？
-
戴夫
---------------------------（播出结束）--------- ------------------
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org
The Postgres manual says:
The AT TIME ZONE construct allows conversions of time stamps to
different time zones.
I''d guess most people would think what''s meant here is something like
"unit conversion", and that the timestamp value stays the same (much
like 2 feet becomes 24 inches when it''s being "converted"). But:
# SELECT NOW() = NOW() AT TIME ZONE ''UTC'';
?column?
----------
f
(1 row)
Compare with:
# select timestamptz ''2004-11-01 12:00:00-05'' =
timestamptz ''2004-11-01 17:00:00-00'';
?column?
----------
t
(1 row)
The question is: does AT TIME TIME ZONE already do what it''s supposed to
do currently?
--
dave
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
-
Martijn van Oosterhout< kl *****@svana.org> http://svana.org/kleptog/ 专利。 ñ。天才是5％的灵感和95％的汗水。专利是一种工具，用于完成5％的工作，然后坐在那里等待某人其他95％，这样你就可以起诉他们。
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
----- BEGIN PGP SIGNATURE -----
版本：GnuPG v1.0.6（GNU / Linux）
评论：有关信息请参阅 http://www.gnupg.org
iD8DBQFBhjW9Y5Twig3Ge + YRApSqAKC6A17uNr / dy4QAOtgp6wPJwDiwTQCg2v84
fZUBd2lCMUJmo4km4XGeL6k =
= Xp4H
----- END PGP SIGNATURE -----
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBhjW9Y5Twig3Ge+YRApSqAKC6A17uNr/dy4QAOtgp6wPJwDiwTQCg2v84
fZUBd2lCMUJmo4km4XGeL6k=
=Xp4H
-----END PGP SIGNATURE-----
你误解了TIMESTAMP WITH TIMEZONE类型，它没有存储
你给它的时区，它只是一个时间点。在TIMEZONE上说
只需将它转换为TIMESTAMP而不用TIMEZONE当地时间
在您给出的时区内。所以你在宣传不同的东西。请参阅：
＃选择now（），now（）在时区''UTC'';
now |时区
------------------------------- + --------- -------------------
2004-11-01 14：07：24.563239 + 01 | 2004-11-01 13：07：24.563239
（1排）
他们有不同的时间，一个有时区，另一个没有。
比较这些可能会将您的本地时区添加到
右边的那个，这意味着结果是错误的。
＃ select（now（）在时区''UTC''）:: timestamptz;
timezone
--------------- ----------------
2004-11-01 13：10：01.136295 + 01
（1行）
希望这能为你澄清...
周一，2004年11月1日下午08:02:33 +0700，David Garamond写道：
You misunderstand the TIMESTAMP WITH TIMEZONE type, it doesn''t store
the timezone you gave it, it''s just a point in time. Saying AT TIMEZONE
just converts it to a TIMESTAMP WITHOUT TIMEZONE with the local time it
was in the timezone you gave it. So you are complaring different
things. See:
# select now(), now() at time zone ''UTC'';
now | timezone
-------------------------------+----------------------------
2004-11-01 14:07:24.563239+01 | 2004-11-01 13:07:24.563239
(1 row)
They have different hours and one has a timezone and the other doesn''t.
Comparing these probably adds your local timezone to the one on the
right which means the result is false.
# select (now() at time zone ''UTC'')::timestamptz;
timezone
-------------------------------
2004-11-01 13:10:01.136295+01
(1 row)
Hope this clarifies it for you...
On Mon, Nov 01, 2004 at 08:02:33PM +0700, David Garamond wrote:
抱歉，点击发送太早......
David Garamond写道：
Sorry, hit Sent too early...
David Garamond wrote:
Postgres手册说：
AT TIME ZONE构造允许将时间戳转换为不同的时区。
我猜大多数人都会认为这里的含义是什么类似
单位转换，a并且时间戳值保持不变（当它被转换时，很多像2英尺变成24英寸）。但是：
#NELING NOW（）=现在（）在时区''UTC'';
？栏？
----------
f
（1排）
比较：
＃select timestamptz''2004-11-01 12：00：00-05''=
timestamptz''2004-11-01 17：00：00-00'';
？专栏？
----------
t
（ 1行）
问题是：AT TIME TIME ZONE已经做了它原本应该做的事情吗？
-
戴夫
---------------------------（播出结束）--------- ------------------
提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org
The Postgres manual says:
The AT TIME ZONE construct allows conversions of time stamps to
different time zones.
I''d guess most people would think what''s meant here is something like
"unit conversion", and that the timestamp value stays the same (much
like 2 feet becomes 24 inches when it''s being "converted"). But:
# SELECT NOW() = NOW() AT TIME ZONE ''UTC'';
?column?
----------
f
(1 row)
Compare with:
# select timestamptz ''2004-11-01 12:00:00-05'' =
timestamptz ''2004-11-01 17:00:00-00'';
?column?
----------
t
(1 row)
The question is: does AT TIME TIME ZONE already do what it''s supposed to
do currently?
--
dave
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
-
Martijn van Oosterhout< kl *****@svana.org> http://svana.org/kleptog/ 专利。 ñ。天才是5％的灵感和95％的汗水。专利是一种工具，用于完成5％的工作，然后坐在那里等待某人其他95％，这样你就可以起诉他们。
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
----- BEGIN PGP SIGNATURE -----
版本：GnuPG v1.0.6（GNU / Linux）
评论：有关信息请参阅 http://www.gnupg.org
iD8DBQFBhjW9Y5Twig3Ge + YRApSqAKC6A17uNr / dy4QAOtgp6wPJwDiwTQCg2v84
fZUBd2lCMUJmo4km4XGeL6k =
= Xp4H
----- END PGP SIGNATURE -----
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBhjW9Y5Twig3Ge+YRApSqAKC6A17uNr/dy4QAOtgp6wPJwDiwTQCg2v84
fZUBd2lCMUJmo4km4XGeL6k=
=Xp4H
-----END PGP SIGNATURE-----
Martijn van Oosterhout写道：
Martijn van Oosterhout wrote:
你误解了TIMESTAMP WITH TIMEZONE类型，它没有存储
时区你给了它，它只是一个时间点。在TIMEZONE说吧
只是将它转换为TIMESTAMP而不是TIMEZONE，当地时间
在您给出的时区内。所以你在宣传不同的东西。请参阅：
You misunderstand the TIMESTAMP WITH TIMEZONE type, it doesn''t store
the timezone you gave it, it''s just a point in time. Saying AT TIMEZONE
just converts it to a TIMESTAMP WITHOUT TIMEZONE with the local time it
was in the timezone you gave it. So you are complaring different
things. See:
你是对的，我忘记了时区在时间戳上的价值目前
会返回一个时间戳，而不是时间戳。
我确实知道目前timestamptz没有存储时区
偏移/时区名称（因此时间戳和时间戳都要求
相同的存储量，8个字节）。但我相信这可能不会在未来如此b / b
。所以问题仍然存在，AT TIME ZONE已经做了它应该做的事情（根据SQL标准，那样）或将会改变行为在将来？ AT TIME ZONE将来会返回
timestamptz而不是时间戳，并且与'=''运算符相比，转换后
时间戳值是否相同？
-
dave
------------------ ---------（广播结束）---------------------------
提示7：别忘了增加你的免费空间地图设置
You''re right, I forgot that AT TIME ZONE on timestamptz value currently
returns a timestamp, not timestamptz.
I do realize that currently timestamptz doesn''t store the timezone
offset/timezone name (thus timestamp and timestamptz both require the
same amount of storage, 8 bytes). But I believe this probably won''t be
so in the future. So the question remains, does AT TIME ZONE already do
what it''s supposed to do (according to SQL standard, that is) or will
the behaviour be changed in the future? Will AT TIME ZONE returns
timestamptz in the future instead of timestamp, and will the "converted"
timestamp value be the same if compared with ''='' operator?
--
dave
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
这篇关于在时区：“转换”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
