评论时区和区间类型 [英] Comment on timezone and interval types
问题描述
最近有一些关于将时区附加到
的时间戳以及其他一些关于在区间类型中包含''day''部分
的讨论。这两个功能相互影响，因为如果你在时间戳上添加一个日期，结果可能取决于时间戳应该是什么时区
使用与时间戳相关的时间区域比使用时区GUC或
固定时区UTC更有意义。
---------------------------（广播结束）---------------- -----------
提示9：如果您的
加入列的数据类型，计划程序将忽略您选择索引扫描的愿望不匹配
Recently there has been some discussion about attaching a timezone to
a timestamp and some other discussion about including a ''day'' part
in the interval type. These two features impact each other, since
if you add a ''day'' to a timestamp the result can depend on what timezone
the timestamp is supposed to be in. It probably makes more sense to use
a timezone associated with the timestamp than say the timezone GUC or the
fixed timezone UTC.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
推荐答案
2004年10月23日星期六06:49:15 -0500，Bruno Wolff III写道：
On Sat, Oct 23, 2004 at 06:49:15PM -0500, Bruno Wolff III wrote:
最近有一些关于将时区附加到时间戳的讨论以及关于在区间类型中包含日部分的其他讨论。这两个功能相互影响，因为如果你在时间戳中加上'day'，结果可能取决于时间戳应该在什么时区。使用它可能更有意义
与时间戳相关的时区，而不是时区GUC或
固定时区UTC。
我同意。我能想到的一个问题是，如果你将每个时间戳存储为一个（秒，时区）对，那么存储要求就会膨胀，
因为时区可能就像"澳大利亚/悉尼"这将为表格中的每个值重复
。我不知道如何轻松地处理
，因为时区没有唯一的标识符和
没有隐含的订单。
我能想到的唯一解决方案是让initdb创建一个pg_timezones
表，它为它找到的每个时区分配一个OID。然后类型可以
使用它。
我认为这实际上是一个很好的解决方案，任何想法？
-
Martijn van Oosterhout< kl ***** @ svana.org> http://svana.org/kleptog/ 专利。 ñ。天才是5％的灵感和95％的汗水。专利是一种工具，用于完成5％的工作，然后坐在那里等待某人其他95％，这样你就可以起诉他们。
Recently there has been some discussion about attaching a timezone to
a timestamp and some other discussion about including a ''day'' part
in the interval type. These two features impact each other, since
if you add a ''day'' to a timestamp the result can depend on what timezone
the timestamp is supposed to be in. It probably makes more sense to use
a timezone associated with the timestamp than say the timezone GUC or the
fixed timezone UTC.
I agree. One issue I can think of is that if you store each timestamp
as a (seconds,timezone) pair, the storage requirements will balloon,
since timezone can be something like "Australia/Sydney" and this will
be repeated for every value in the table. I don''t know how to deal
easily with this since there is no unique identifier to timezones and
no implicit order.
The only solution I can think of is have initdb create a pg_timezones
table which assigns an OID to each timezone it finds. Then the type can
use that.
I think this is a good solution actually, any thoughts?
--
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
iD8DBQFBe5k4Y5Twig3Ge + YRAuYBAKCmRpYp1RbnZdxnMGOups 17rBL6XgCguK63
/ pyjsZa3JI42dKZSQC7BstU =
= 0Yqk
----- END PGP SIGNATURE -----
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBe5k4Y5Twig3Ge+YRAuYBAKCmRpYp1RbnZdxnMGOups 17rBL6XgCguK63
/pyjsZa3JI42dKZSQC7BstU=
=0Yqk
-----END PGP SIGNATURE-----
Martijn，
我同意。我能想到的一个问题是，如果你将每个时间戳存储为（秒，时区）对，存储要求将会膨胀，因为时区可能类似于澳大利亚/悉尼。这将对表中的每个值重复。由于没有时区的唯一标识符和
没有隐含的顺序，我不知道如何轻松处理
我能想到的唯一解决方案是initdb创建一个pg_timezones
表，为它找到的每个时区分配一个OID。然后类型可以使用它。
我认为这是一个很好的解决方案，任何想法？
I agree. One issue I can think of is that if you store each timestamp
as a (seconds,timezone) pair, the storage requirements will balloon,
since timezone can be something like "Australia/Sydney" and this will
be repeated for every value in the table. I don''t know how to deal
easily with this since there is no unique identifier to timezones and
no implicit order.
The only solution I can think of is have initdb create a pg_timezones
table which assigns an OID to each timezone it finds. Then the type can
use that.
I think this is a good solution actually, any thoughts?
使用OID'这是一个好主意，但我认为必须维护一个已知的OID映射时间区域的规范列表，并附带
PostgreSQL核心。
如果在initdb时生成OID，那么
OID'在使用不同版本的
的数据库之间会有很大差异。
PostgreSQL。这反过来会对数据产生一些负面影响
交换。
问候，
Thomas Hallgren
---------------------------（广播结束）---------------- -----------
提示5：您查看了我们广泛的常见问题解答吗？
http://www.postgresql.org/docs/faqs/FAQ.html
Using OID''s is a good idea, but I think a canonical list of known
timezone to OID mappings must be maintained and shipped with the
PostgreSQL core.
If OID''s are generated at initdb time, there''s a great risk that the
OID''s will differ between databases using different versions of
PostgreSQL. That in turn will have some negative implications for data
exchange.
Regards,
Thomas Hallgren
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
2004年10月27日星期三上午09:21:39 +0200，Thomas Hallgren写道：
On Wed, Oct 27, 2004 at 09:21:39AM +0200, Thomas Hallgren wrote:
Martijn，
I同意。我能想到的一个问题是，如果你将每个时间戳存储为（秒，时区）对，存储要求将会膨胀，因为时区可能类似于澳大利亚/悉尼。这将对表中的每个值重复。由于没有时区的唯一标识符和
没有隐含的顺序，我不知道如何轻松处理
我能想到的唯一解决方案是initdb创建一个pg_timezones
表，为它找到的每个时区分配一个OID。然后类型可以使用它。
我认为这是一个很好的解决方案，任何想法？
使用OID是一个好主意，但我认为必须维护一个已知的OID映射时区的规范列表，并附带了PostgreSQL核心。
I agree. One issue I can think of is that if you store each timestamp
as a (seconds,timezone) pair, the storage requirements will balloon,
since timezone can be something like "Australia/Sydney" and this will
be repeated for every value in the table. I don''t know how to deal
easily with this since there is no unique identifier to timezones and
no implicit order.
The only solution I can think of is have initdb create a pg_timezones
table which assigns an OID to each timezone it finds. Then the type can
use that.
I think this is a good solution actually, any thoughts?
Using OID''s is a good idea, but I think a canonical list of known
timezone to OID mappings must be maintained and shipped with the
PostgreSQL core.
如何有一个已知时区的规范列表如果每个
操作系统都有它自己的列表。也许你可以提供一个基本列表，
但是你必须允许人们自己制作。
如果在initdb时生成OID'，那么''使用不同版本的PostgreSQL，数据库之间的差异很大。这反过来可能会对数据交换产生一些负面影响。
我对此表示怀疑，OID永远不会输出。类型，触发器，函数
等都有OID，这些OID在任何地方都不会出现在任何输出中，所以为什么
应该这些。由于PostgreSQL不支持你在不同安装之间复制原始数据文件的任何部分，更不用说
不同的版本，我认为数据交换的问题是不是
问题。
祝你有愉快的一天，
-
Martijn van Oosterhout < KL ***** @ svana.org> http://svana.org/kleptog/ 专利。 ñ。天才是5％的灵感和95％的汗水。专利是一种工具，用于完成5％的工作，然后坐在那里等待某人其他95％，这样你就可以起诉他们。
How can there be a "canonical list of known timezones" if every
operating system has it''s own list. Maybe you can provide a base list,
but you have to allow for people to make their own.
If OID''s are generated at initdb time, there''s a great risk that the
OID''s will differ between databases using different versions of
PostgreSQL. That in turn might have some negative implications for data
exchange.
I doubt it, the OIDs would never be output. Types, triggers, functions
etc all have OIDs that never appear in any output anywhere, so why
should these. Since PostgreSQL doesn''t support you to copying any part
of the raw data files between different installations, let alone
different versions, I think the issues with data exchange are not a
problem.
Have a nice day,
--
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
iD8DBQFBf2OzY5Twig3Ge + YRAscdAJ4plhHOrahW / HaEJ2XRZD / W7r8 + tQCgtzYp
2OPYF70KXLQhmlzcX4yNYUA =
= e / XA
----- END PGP SIGNATURE- ----
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFBf2OzY5Twig3Ge+YRAscdAJ4plhHOrahW/HaEJ2XRZD/W7r8+tQCgtzYp
2OPYF70KXLQhmlzcX4yNYUA=
=e/XA
-----END PGP SIGNATURE-----
这篇关于评论时区和区间类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
