Oracle UTC 时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14983266/
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 UTC Time
提问by Harry
I'm trying to read an Oracle TIMESTAMP WITH TIMEZONEfrom a database in UTC-5 (NY) as UTC.
我正在尝试TIMESTAMP WITH TIMEZONE从 UTC-5 (NY) 的数据库中读取 Oracle作为 UTC。
Oracle is driving me crazy:
Oracle 快把我逼疯了：
SELECT
from_tz(MAX(TIMESTAMPWITHTIMEZONE),'UTC'),
SYS_EXTRACT_UTC(MAX(TIMESTAMPWITHTIMEZONE)),
SYS_EXTRACT_UTC(systimestamp),
SYSTIMESTAMP AT TIME ZONE 'UTC'
FROM TABLE
Results:
结果：
SYS_EXTRACT_UTC(systimestamp)gives me:2013-02-20 14:59:04, which is probably right.SYSTIMESTAMP AT TIME ZONE 'UTC'gives me:2013-02-20 15:59:04which is my own local Berlin - whatever
SYS_EXTRACT_UTC(systimestamp)给我：2013-02-20 14:59:04，这可能是正确的。SYSTIMESTAMP AT TIME ZONE 'UTC'给我：2013-02-20 15:59:04这是我自己的柏林 - 随便
Now I want to have TIMESTAMPWITHTIMEZONE (TIMESTAMP(6)) as UTC
现在我想将 TIMESTAMPWITHTIMEZONE (TIMESTAMP(6)) 作为 UTC
SYS_EXTRACT_UTC(MAX(TIMESTAMPWITHTIMEZONE))is2013-02-20 08:55:01from_tz(MAX(TIMESTAMPWITHTIMEZONE),'UTC')is2013-02-20 10:55:01
SYS_EXTRACT_UTC(MAX(TIMESTAMPWITHTIMEZONE))是2013-02-20 08:55:01from_tz(MAX(TIMESTAMPWITHTIMEZONE),'UTC')是2013-02-20 10:55:01
Srly. Oracle. I want UTC.
亲爱的。甲骨文。我想要UTC。
Which one is the right one? Or is there a better way?
哪一个是正确的？或者，还有更好的方法？
回答by Vincent Malgrat
The functions are different:
功能不同：
SYS_EXTRACT_UTCconverts aTIMESTAMP WITH TIMEZONEto aTIMESTAMP(with inferred but absent timezone=UTC).FROM_TZconverts aTIMESTAMPto aTIMESTAMP WITH TIMEZONE
SYS_EXTRACT_UTC将 a 转换TIMESTAMP WITH TIMEZONE为 aTIMESTAMP（推断出但不存在时区 = UTC）。FROM_TZ将 a 转换TIMESTAMP为 aTIMESTAMP WITH TIMEZONE
These functions when applied to a single value will in general return a different result:
这些函数在应用于单个值时通常会返回不同的结果：
SQL> SELECT sys_extract_utc(localtimestamp) ext,
2 from_tz(localtimestamp, 'UTC') from_tz
3 FROM dual;
EXT FROM_TZ
--------------------- ------------------------
2013/02/20 15:34:24 2013/02/20 16:34:24 UTC
In the first case the TIMESTAMPis implicitlygiven the timezone of the server and then transformed into the equivalent timestamp at the UTC timezone. Note that in general you should stay away from implicit conversions.
在第一种情况中TIMESTAMP被隐式地给出的服务器的时区，然后转化进在UTC时区的等效时间戳。请注意，通常您应该远离隐式转换。
In the second case there is no computation between timezones: the FROM_TZfunction adds the geographical location to a point in time variable.
在第二种情况下，时区之间没有计算：该FROM_TZ函数将地理位置添加到时间点变量中。
By the way there is something missing in your example: you can't apply the FROM_TZfunction on a variable of type TIMESTAMP WITH TIMEZONE(tested on 9ir2 and 11ir2):
顺便说一下，您的示例中缺少一些内容：您不能将该FROM_TZ函数应用于类型变量TIMESTAMP WITH TIMEZONE（在 9ir2 和 11ir2 上测试）：
SQL> select from_tz(systimestamp, 'UTC') from dual;
select from_tz(systimestamp, 'UTC') from dual
ORA-00932: inconsistent datatypes:
expected TIMESTAMP got TIMESTAMP WITH TIME ZONE
Edit following comment:
编辑以下评论：
In your case assuming that your column are of time TIMESTAMP, and knowing that they refer to the NY timezone, you could use the AT TIME ZONEexpression to convert to UTC:
在您的情况下，假设您的列是 time TIMESTAMP，并且知道它们指的是纽约时区，您可以使用AT TIME ZONE表达式转换为UTC：
SQL> SELECT localtimestamp,
2 from_tz(localtimestamp, 'America/New_York') AT TIME ZONE 'UTC' utc
3 FROM dual;
LOCALTIMESTAMP UTC
--------------------- ------------------------
2013/02/20 17:09:09 2013/02/20 22:09:09 UTC
回答by Lukasz Szozda
From Oracle 18c you could use TO_UTC_TIMESTAMP_TZfunction:
从 Oracle 18c 开始，您可以使用TO_UTC_TIMESTAMP_TZ函数：
The TO_UTC_TIMESTAMP_TZ function converts any valid ISO 8601 date represented as a string into a TIMESTAMP WITH TIMEZONE, which can optionally be used as input to the SYS_EXTRACT_UTC function.
TO_UTC_TIMESTAMP_TZ 函数将任何以字符串表示的有效 ISO 8601 日期转换为 TIMESTAMP WITH TIMEZONE，它可以选择性地用作 SYS_EXTRACT_UTC 函数的输入。
SELECT TO_UTC_TIMESTAMP_TZ(col_name)
FROM tab_name;
