SQL Oracle如何将UTC时间转换为本地时间（缺少偏移量信息）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38346516/
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 how to convert time in UTC to the local time (offset information is missing)
提问by Max_Salah
I have a table which contains a date column. I think that the date in that column is saved in UTC.
我有一个包含日期列的表。我认为该列中的日期以 UTC 格式保存。
I would like when the date is retrieved that it is printed in local time. That means when I call the date from Germany, the result should be something like this:
我希望在检索日期时以当地时间打印。这意味着当我从德国调用日期时，结果应该是这样的：
2015-04-29 11:24:06 +0200UTC EUROPE/BERLIN
I tried the following sql:
我尝试了以下sql：
SELECT TO_CHAR(CAST(dateColum as TIMESTAMP WITH LOCAL TIME ZONE), 'YYYY-MM-DD HH24:MI:SS TZR') from myTable;
the result looks like this:
结果如下所示：
2015-04-29 11:24:06 EUROPE/BERLIN
+/- offset is missing.
缺少 +/- 偏移。
Any idea?
任何的想法？
回答by MT0
Oracle Setup:
甲骨文设置：
CREATE TABLE table_name ( value ) AS
SELECT DATE '2016-07-13' FROM DUAL;
Query:
查询：
SELECT TO_CHAR(
FROM_TZ( CAST( value AS TIMESTAMP ), 'UTC' )
AT TIME ZONE 'EUROPE/BERLIN',
'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
) AS berlin_time
FROM table_name;
Output:
输出：
BERLIN_TIME
----------------------------------------
2016-07-13 02:00:00 +02:00 EUROPE/BERLIN
Query 2:
查询 2：
SELECT TO_CHAR(
FROM_TZ( CAST( value AS TIMESTAMP ), 'UTC' ) AT LOCAL,
'YYYY-MM-DD HH24:MI:SS TZH:TZM TZR'
) AS local_time
FROM table_name;
Output:
输出：
LOCAL_TIME
----------------------------------------
2016-07-13 02:00:00 +02:00 EUROPE/BERLIN
