PostgreSQL 中的夏令时时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10383108/
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
Time zone with daylight savings times in PostgreSQL
提问by Debaser
We're deploying our own stream gauges (a lot like this USGS gauge: http://waterdata.usgs.gov/usa/nwis/uv?site_no=03539600) so us kayakers know whether or not there's enough water to paddle the stream and don't waste time and gas to drive out there. We hope install a few of these across the southeast whitewater region which spans the eastern and central time zones.
我们正在部署我们自己的流量测量仪（很像这个 USGS 测量仪：http: //waterdata.usgs.gov/usa/nwis/uv?site_no=03539600）所以我们的皮划艇运动员知道是否有足够的水来划水不要浪费时间和汽油开车出去。我们希望在跨越东部和中部时区的东南白水地区安装其中的一些。
I'm storing the time a record is inserted using the default value of current_time for the record. I'd like to later display the data using the MM/DD/YYYY HH12:MI AM TZformat, which outputs reading like 03/12/2012 01:00 AM CDT. I'd also like for the output to be aware of changes in day light savings time, so the last part of the previous sentence would change between CST and CDT when we 'spring forward' and 'fall back'. This change occurred on 3/11/2012 this year and I've included dates on both sides of this DST line below. I'm using my Windows 7 laptop for development and we will later be deploying on a Unix box. Postgres has apparently detected that my Windows computer is set to eastern US time zone. I'm trying this with a 'timestamp without time zone' field and a 'timestamp with time zone' field but can't get it to work.
我正在使用记录的 current_time 的默认值存储插入记录的时间。我想稍后使用该MM/DD/YYYY HH12:MI AM TZ格式显示数据，该格式输出类似03/12/2012 01:00 AM CDT. 我还希望输出能够了解夏令时的变化，因此当我们“向前”和“后退”时，上一句的最后一部分会在 CST 和 CDT 之间发生变化。这一变化发生在今年 2012 年 3 月 11 日，我在下面这条 DST 线的两侧都包括了日期。我正在使用我的 Windows 7 笔记本电脑进行开发，我们稍后将在 Unix 机器上进行部署。Postgres 显然检测到我的 Windows 计算机设置为美国东部时区。我正在尝试使用“不带时区的时间戳”字段和“带时区的时间戳”
I've tried using 'at time zone' in my selects and every thing is working until it's time to display the time zone. The actual hour is part of the time stamp is correctly subtracted by an hour when I ask for the time in CDT. But EDT is displayed in the output.
我已经尝试在我的选择中使用“在时区”，并且一切正常，直到需要显示时区。当我在 CDT 中询问时间时，实际小时数是时间戳的一部分被正确减去一小时。但 EDT 显示在输出中。
SELECT reading_time as raw,
reading_time at time zone 'CDT',
to_char(reading_time at time zone 'CDT',
'MM/DD/YYYY HH12:MI AM TZ') as formatted_time
FROM readings2;
"2012-04-29 17:59:35.65";"2012-04-29 18:59:35.65-04";"04/29/2012 06:59 PM EDT"
"2012-04-29 17:59:40.19";"2012-04-29 18:59:40.19-04";"04/29/2012 06:59 PM EDT"
"2012-03-10 00:00:00";"2012-03-10 00:00:00-05";"03/10/2012 12:00 AM EST"
"2012-03-11 00:00:00";"2012-03-11 00:00:00-05";"03/11/2012 12:00 AM EST"
"2012-03-12 00:00:00";"2012-03-12 01:00:00-04";"03/12/2012 01:00 AM EDT"
I'm storing the time zone that each of our gauges is located in a character varying field a separate table. I considered just appending this value to the end of the time output, but I want it to change from from CST to CDT without my intervention.
我将每个仪表所在的时区存储在一个单独的表中的字符变化字段中。我考虑过将此值附加到时间输出的末尾，但我希望它在没有我干预的情况下从 CST 更改为 CDT。
Thanks for your help.
谢谢你的帮助。
回答by Guan Yang
Instead of using time zone names like CDT or CST, you could consider using full Olsen-style time zone names. In the case of central time, you could choose a time zone. Either one that matches your location, such as America/Chicago, or just US/Central. This ensures PostgreSQL uses the Olsen tz database to automatically figure out whether daylight saving time applies at any given date.
您可以考虑使用完整的奥尔森风格时区名称，而不是使用 CDT 或 CST 等时区名称。在中部时间的情况下，您可以选择一个时区。与您的位置相匹配的一个，例如America/Chicago，或仅US/Central。这确保 PostgreSQL 使用 Olsen tz 数据库自动确定夏令时是否适用于任何给定日期。
回答by kgrittn
You definitely want a TIMESTAMP WITH TIME ZONEcolumn (which is also known as timestamptzin PostgreSQL). That will store the timestamp in UTC, so that it represents a particular moment in time. Contrary to what the name suggests, it does notsave a time zone in the column -- you can view the retrieved timestamp in the time zone of your choosing with the AT TIME ZONEphrase.
您肯定需要一个TIMESTAMP WITH TIME ZONE列（timestamptz在 PostgreSQL 中也称为列）。这将以UTC格式存储时间戳，以便它代表特定时刻。相反的是，顾名思义，它并没有保存在列时区-您可以查看与您选择的时区检索时间戳AT TIME ZONE短语。
The semantics of TIMESTAMP WITHOUT TIME ZONE are confusing and nearly useless. I strongly recommend you don't use that type at all for what you are describing.
TIMESTAMP WITHOUT TIME ZONE 的语义令人困惑且几乎无用。我强烈建议您根本不要使用该类型来描述您所描述的内容。
I'm really confused by the part of the question which talks about storing the timestamp in a CHARACTER VARYINGcolumn. That seems as though it might be part of the problem. If you can store it in timestamptzright from the start I suspect that you will have fewer problems. Barring that, it would be safest to use the -04 notation for offset from UTC; but that seems like more work to me for no benefit.
关于将时间戳存储在CHARACTER VARYING列中的问题的一部分，我真的很困惑。这似乎是问题的一部分。如果您可以timestamptz从一开始就将其存储，我怀疑您的问题会更少。除此之外，使用 -04 表示法来表示与 UTC 的偏移量是最安全的；但这对我来说似乎没有任何好处。
回答by beldaz
You can create a table of known timezones in the format suggested in Guan Yang's answer, and then use a foreign key column to this table. Valid timezones can be obtained from pg_timezone_namesI've gone into more detail in this related answer.
您可以按照Guan Yang's answer 中建议的格式创建一个已知时区表，然后对该表使用外键列。有效的时区可以从pg_timezone_names我在这个相关的答案中更详细地获得。
