Oracle SQL：将 UTC 转换为 CST
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26636736/
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: convert UTC to CST
提问by user3050672
I would like to convert UTC date/time to local CST.
我想将 UTC 日期/时间转换为本地 CST。
The below function works however it gives 6 hours difference when there should only be 5 hours (until day light saving on 11/2/2014).
下面的函数可以工作，但是当应该只有 5 小时时，它会产生 6 小时的差异（直到 2014 年 11 月 2 日的夏令时）。
CAST((FROM_TZ(CAST(utc_date AS TIMESTAMP),'UTC') AT TIME ZONE 'CST') AS DATE) cst_date
also tried a variation
也尝试了一个变种
to_date(to_char((from_tz(to_timestamp(to_char(utc_date, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') ,'UTC')
at time zone 'CST'),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as cst_date,
回答by Rajesh Chamarthi
Using the "US/Central" as the target timezone seems to produce the right result.
使用“美国/中部”作为目标时区似乎会产生正确的结果。
select from_tz(CAST ('15-oct-2014' AS TIMESTAMP),'GMT') at TIME ZONE 'US/Central' with_daylight_savings,
from_tz(CAST ('15-nov-2014' AS TIMESTAMP),'GMT') at TIME ZONE 'US/Central' without_daylight_savings
from dual;
WITH_DAYLIGHT_SAVINGS WITHOUT_DAYLIGHT_SAVINGS
--------------------------------------------------------------------------------------
14-OCT-14 07.00.00.000000000 PM US/CENTRAL 14-NOV-14 06.00.00.000000000 PM US/CENTRAL
回答by Wernfried Domscheit
Maybe a stupid approach, but what do you get from this query?
也许是一种愚蠢的方法，但是您从这个查询中得到了什么？
SELECT
TO_CHAR((TIMESTAMP '2014-01-01 00:00:00' + LEVEL * INTERVAL '1' DAY) AT TIME ZONE 'America/Chicago', 'yyyy-mm-dd hh24:mi TZH:TZM') AS dst,
TO_CHAR((FROM_TZ(CAST(DATE '2014-01-01' AS TIMESTAMP), 'UTC') + LEVEL * INTERVAL '1' DAY) AT TIME ZONE 'America/Chicago', 'yyyy-mm-dd hh24:mi TZH:TZM') AS dst
FROM dual
CONNECT BY LEVEL <= 365;
Is it as expected?
是否符合预期？
回答by Multisync
Use timezone region instead of timezone abbr ('CST'). You may find the desired timezone here:
使用时区区域而不是时区缩写 ('CST')。您可以在此处找到所需的时区：
SELECT * from v$timezone_names where tzabbrev = 'CST';
Maybe you need 'CST6CDT' instead of 'CST'
也许您需要“CST6CDT”而不是“CST”
