如何知道 postgresql 8.3 中时间戳的时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/553822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to know a timezone of a timestamp in postgresql 8.3
提问by egesuato
I'm using postgresql 8.3 and i would like to know the timezone of a particular timestamp (a column in a table).
我正在使用 postgresql 8.3，我想知道特定时间戳（表中的一列）的时区。
In the documentation i've found the keyword "timezone"
在文档中，我找到了关键字“时区”
But i don't understand how to apply it in a column of table. Is it possible ?
但我不明白如何将它应用到表格的列中。是否可以 ？
回答by Johannes Weiss
I assume you have a column named ctwhich has the type TIMESTAMPTZin the table t. Then you can use:
我假设您有一个名为的列ct，该列具有TIMESTAMPTZ表中的类型t。然后你可以使用：
SELECT EXTRACT(TIMEZONE FROM ct) FROM t;
to get the offset of the timezone in seconds. It that gives you 3600from UTC/GMTthat means either GMT+1, CETor whatever. The value returned depends on your TIMEZONEsetting.
以秒为单位获取时区的偏移量。它给你3600from UTC/GMT这意味着要么GMT+1，CET要么什么。返回的值取决于您的TIMEZONE设置。
Sample (I live in Germany, actual timezone ist GMT+1/CET):
示例（我住在德国，实际时区是GMT+1/ CET）：
test=# select '2008-01-01 12:00:00 GMT+5'::timestamptz;
timestamptz
------------------------
2008-01-01 18:00:00+01
test=# set timezone to 'gmt';
SET
test=# select '2008-01-01 12:00:00 GMT+5'::timestamptz;
timestamptz
------------------------
2008-01-01 17:00:00+00
As you can see it always outputs anything in the configured timezone. So the offset you will get with EXTRACT(TIMEZONE FROM ...)depends on your TIMEZONEsetting. The timezone which was given on INSERTis lost, because it is not worth to be saved. The thing that counts is that everything is correct and that should not depend on the TIMEZONEsetting. PostgreSQL does that very well.
如您所见，它始终输出配置时区中的任何内容。因此，您将获得的偏移量EXTRACT(TIMEZONE FROM ...)取决于您的TIMEZONE设置。给出的时区INSERT丢失了，因为它不值得保存。重要的是一切都是正确的，这不应该取决于TIMEZONE设置。PostgreSQL 在这方面做得很好。
回答by W Strzalka
"PostgreSQL does that very well."
“PostgreSQL 在这方面做得很好。”
I really like PostgreSQL, but in this particular feature it does not do it well. Timezone is not only offset to GMT. Timezone is tight to political rules that implies daylight saving. As there are plenty of timezones with the same offset and different daylight saving rules - when PG forgets about original timezone it looses the information in fact.
我真的很喜欢 PostgreSQL，但是在这个特殊的功能上它做得并不好。时区不仅偏移 GMT。时区严格遵守暗示夏令时的规则。由于有很多时区具有相同的偏移量和不同的夏令时规则 - 当 PG 忘记原始时区时，它实际上会丢失信息。
Personally I store original timezone separately for the dates it matters in the form 'America/New_York'. If anybody has better solution - it's welcome.
我个人将原始时区单独存储为“美国/纽约”形式的重要日期。如果有人有更好的解决方案 - 欢迎使用。
回答by Ashish
In Postgres the original input timezone is lost even if you store the datetime value in timestamp with time zonedata type.
在 Postgres 中，即使您将日期时间值存储在时区数据类型的时间戳中，原始输入时区也会丢失。
Disadvantage:
Say if I have datetime data(along with timezone info) of when people use their facebook app all over the world. Now I store those values in timestamp with time zonedata type. One random day I want to see how heavily people use their FB app in the morning compared to at night and if the same trend is seen across countries.
But wait, I no longer have the timezone information.
Sitting in D.C., what I can see is how the activity panned out all over the world when it was 10am EST.
缺点：
假设我是否有人们在世界各地使用他们的 facebook 应用程序的日期时间数据（以及时区信息）。现在我将这些值存储在时区数据类型的时间戳中。随机的一天，我想看看人们早上与晚上使用他们的 FB 应用程序的程度相比，是否在不同国家/地区都可以看到相同的趋势。但是等等，我不再有时区信息。坐在华盛顿特区，我能看到的是当美国东部标准时间上午 10 点时，这项活动在世界范围内是如何发展的。
Advantage:
If you are comparing two datasources with timestamp of same timezone but one was stored as what the local watch was showing while other stored by converting it to UTC. Here what you can do is just store the timestamp in the timestamptz datatype and tell which timezone it belongs to and then you can compare them easily.
For example,
An activity in PST timezone was recorded at 2014-10-19 10:23:54. Now two separate data sources stored it separately. Datasource1 stored it as 2004-10-19 10:23:54 PST, Datasource2 stored it as 2014-10-19 18:23:54 UTC. If they are stored in the timestamptzdatatype, it will show you the same time when you do
优点：
如果您要比较两个具有相同时区时间戳的数据源，但其中一个存储为本地手表显示的内容，而另一个存储为将其转换为 UTC。在这里您可以做的只是将时间戳存储在 timestamptz 数据类型中并告诉它属于哪个时区，然后您可以轻松地比较它们。
例如，
在 PST 时区的活动记录在2014-10-19 10:23:54。现在两个独立的数据源分别存储它。Datasource1 将其存储为2004-10-19 10:23:54 PST，Datasource2 将其存储为2014-10-19 18:23:54 UTC. 如果它们存储在timestamptz数据类型中，它会在您执行操作的同时向您显示
SELECT datasource1.time, datasource2.time
回答by Dennis
Since the timestampz is recoreded for an instant in time in ZULU/GMT time, which never changes it's offset (since it's the reference), it is not necessary to record the timezone. You need only add/subtract the offset to the GEOPOLITICAL time zone offset in the PAST, PRESENT, or FUTURE.
由于时间戳记是在 ZULU/GMT 时间的瞬间记录的，它永远不会改变它的偏移量（因为它是参考），因此没有必要记录时区。您只需要在过去、现在或未来的地理时区偏移中添加/减去偏移。
You DOneed to know the exact GEOPOLITICAL TIMEZONE in effect at the location the time applies to at the moment applies to for PAST and PRESENT purposes.
您DO需要知道在位置效果确切地缘TIMEZONE时间适用于目前适用于对过去和现在的目的。
For future instances in time purposes, this gets more problematic, maybe. It should still work though. Think of sunset. If some location earth has a sunset @ midnight ZULU time (somewhere over the Atlantic Ocean or Northern Canada to Alaska in winter in the Northern Hemisphere), and that time is assumed to be 8 PM ( -4:00 offset) in that location at the moment you record it in the system and you record it as 'winter-date-in-future 8:00 PM' it will be recorded as 24:00 GMT in the database.
对于未来的时间目的，这可能会变得更加成问题。不过它应该仍然有效。想想日落。如果地球的某个位置在 ZULU 时间午夜（从大西洋或加拿大北部到北半球冬季的阿拉斯加的某个地方）有日落，并且该时间假定为该位置的晚上 8 点（偏移 -4:00）当您将其记录在系统中并将其记录为“未来冬季 8:00 PM”时，它将在数据库中记录为格林威治标准时间 24:00。
Now, that location on earth gets a hair up its *ss and thumbs its nose at geographical related time reckoning, and calls their time zone - '+11:55'. So for them when it's midnight in England (GMT midnight) they WANT to call it 11:55 AM, totally their choice. When any computer wants to display that date in the future for that location (i.e. that geopolitical timezone), they will call it 11:55 AM, even if the sun is setting. And of course, it WILL be the day AHEAD of the day you planned it :-) Their problem.
现在，地球上的那个位置在与地理相关的时间计算中竖起一根头发并竖起大拇指，并称他们的时区为“+11:55”。因此，对于他们来说，当英格兰的午夜（格林威治标准时间午夜）时，他们想将其称为上午 11:55，这完全是他们的选择。当任何计算机想要显示该位置（即该地缘时区）的未来日期时，即使太阳正在落山，他们也会将其称为上午 11:55。当然，这将是您计划的那一天的前一天:-) 他们的问题。
