Oracle SQL：获取给定日期的时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26717247/
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
Oracle SQL: get timezone for a given date
提问by Devang Thakkar
How can the timezone be determined accurately for a given date (DATE data_type column) based on the following information?
如何根据以下信息准确确定给定日期（DATE data_type 列）的时区？
a) DATE columns do not store timezone information.
a) DATE 列不存储时区信息。
b) Server timezone is either EST or EDT depending on whether date light saving is in effect or not
b) 服务器时区是 EST 或 EDT，具体取决于日期光节约是否生效
c) The current timezone offset can be easily fetched by SQL:
c) 可以通过 SQL 轻松获取当前时区偏移量：
SELECT TO_CHAR(SYSTIMESTAMP,'TZH:TZM') FROM DUAL
SELECT TO_CHAR(SYSTIMESTAMP,'TZH:TZM') FROM DUAL
Assuming that timezone for today (3-Nov-2014) is EST, how can we determine what was the timezone in effect (i.e. EST or EDT) for a given date (e.g. 21-Oct-2014) programatically?
假设今天（2014 年 11 月 3 日）的时区是 EST，我们如何以编程方式确定给定日期（例如 21-Oct-2014）的有效时区（即 EST 或 EDT）？
回答by Matt Johnson-Pint
Oracle has support for IANA time zones. The US Eastern time zones is represented by "America/New_York".
Oracle 支持IANA 时区。美国东部时区由 表示"America/New_York"。
You can use FROM_TZto create a TIMESTAMP WITH TIME ZONEfrom a TIMESTAMPvalue. (If you're starting from DATEthen you should first cast to TIMESTAMP.)
您可以使用从值FROM_TZ创建一个。（如果您从那时开始，您应该首先转换为。）TIMESTAMP WITH TIME ZONETIMESTAMPDATETIMESTAMP
Then you can use TO_CHARwith either 'TZH:TZM'to get an offset, or with 'TZD'to get an abbreviation (such as EST or EDT).
然后您可以使用TO_CHARwith'TZH:TZM'获取偏移量，或使用 with'TZD'获取缩写（例如 EST 或 EDT）。
For example:
例如：
SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-02 00:00:00', 'America/New_York'),'TZD')
FROM DUAL; -- Output: 'EDT'
SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-02 00:00:00', 'America/New_York'),'TZH:TZM')
FROM DUAL; -- Output: '-04:00'
SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-03 00:00:00', 'America/New_York'),'TZD')
FROM DUAL; -- Output: 'EST'
SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2014-11-03 00:00:00', 'America/New_York'),'TZH:TZM')
FROM DUAL; -- Output: '-05:00'
