将 XSD 日期 xs:dateTime 转换为 Oracle 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3549068/
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
Converting a XSD date xs:dateTime to an Oracle Date
提问by MoreCoffee
I need to convert a date from this format:
我需要从此格式转换日期：
2002-10-10T12:00:00-05:00(xs:dateTime as defined in XML)
2002-10-10T12:00:00-05:00（在 XML 中定义的 xs:dateTime）
to an Oracle date.
到Oracle 日期。
I'm used to using this in PL/SQL: to_date('date here', 'yyyymmdd'), is there a way to convert this while keeping the time zone info?
我习惯在 PL/SQL 中使用它：to_date('date here', 'yyyymmdd')，有没有办法在保留时区信息的同时转换它？
Thanks
谢谢
回答by Rob van Wijk
A short answer:
简短的回答：
SQL> select to_timestamp_tz('2002-10-10T12:00:00-05:00','yyyy-mm-dd"T"hh24:mi:sstzh:tzm')
2 from dual
3 /
TO_TIMESTAMP_TZ('2002-10-10T12:00:00-05:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM
---------------------------------------------------------------------------
10-OCT-02 12.00.00.000000000 PM -05:00
1 row selected.
Regards, Rob.
问候，罗伯。
回答by APC
Oracle dates don't have timezone information. You'll need to use a TIMESTAMP datatype instead.
Oracle 日期没有时区信息。您需要改用 TIMESTAMP 数据类型。
It works something like this:
它的工作原理是这样的：
SQL> desc tz
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TS TIMESTAMP(6) WITH TIME ZONE
TNOW TIMESTAMP(6) WITH TIME ZONE
SQL> insert into tz
2 values (1
3 , to_timestamp_tz('2002-10-10 12:00:00-05:00'
4 , 'YYYY-MM-DD HH24:MI:SSTZH:TZM')
5 , systimestamp)
6 /
1 row created.
SQL> select * from tz
2 /
ID
----------
TS
---------------------------------------------------------------------------
TNOW
---------------------------------------------------------------------------
1
10-OCT-02 12.00.00.000000 -05:00
23-AUG-10 17.37.06.502000 +01:00
SQL>
Note, there is the tricky issue of the Tin the XSD notation. That hurls a ORA-01858exception, because it's not a valid format in Oracle. I'm sure there is a workaround, but it currently escapes me.
请注意，XSD 表示法中的T有一个棘手的问题。这会引发ORA-01858异常，因为它在 Oracle 中不是有效格式。我确定有一种解决方法，但目前我无法解决。
Well, one workaround is to apply SUBSTR() function sto split open the two parts of the timestamp, as Bob shows. But there ought to be a more elegant way.
好吧，一种解决方法是应用 SUBSTR() 函数来拆分时间戳的两个部分，如 Bob 所示。但应该有更优雅的方式。
It probably doesn't qualify as "elegant" but as it's a string we can use a substitution function to get rid of the annoying T:
它可能不符合“优雅”的条件，但由于它是一个字符串，我们可以使用替换函数来摆脱烦人的 T：
SQL> insert into tz
2 values (2
3 , to_timestamp_tz(translate('2003-10-10T12:00:00-05:00', 'T', ' ')
4 , 'YYYY-MM-DD HH24:MI:SSTZH:TZM')
5 , systimestamp)
6 /
1 row created.
SQL> select * from tz
2 /
ID
----------
TS
---------------------------------------------------------------------------
TNOW
---------------------------------------------------------------------------
1
10-OCT-02 12.00.00.000000 -05:00
23-AUG-10 17.37.06.502000 +01:00
2
10-OCT-03 12.00.00.000000 -05:00
23-AUG-10 17.53.37.113000 +01:00
SQL>
But given all the effort Oracle have put into XMLDB it is rather annoying that there isn't a tidier solution.
但是考虑到 Oracle 在 XMLDB 中付出的所有努力，没有更简洁的解决方案是相当烦人的。
"I dont understand how you get -05:00."
“我不明白你是怎么得到 -05:00 的。”
In my original sample I use a format mask of 'YYYY-MM-DD HH24:MI:SS-TZH:TZM'. This interprets the -in the time zone as a separator nota minus sign. Consequently it returned +05:00. I have since corrected my code sample to remove that last dash. Now the timezone is correctly rendered as -05:00. Sorry for any confusion.
在我的原始示例中，我使用的格式掩码为'YYYY-MM-DD HH24:MI:SS-TZH:TZM'. 这-将时区中的解释为分隔符而不是减号。因此它返回+05:00。我已经更正了我的代码示例以删除最后一个破折号。现在时区正确呈现为 -05:00。很抱歉有任何混淆。
回答by Bob Jarvis - Reinstate Monica
Here's an example of how to convert this to DATE and TIMESTAMP WITH TIME ZONE data types. Note that with the DATE type the time zone information is lost (in the conversion from TIMESTAMP WITH TIME ZONE):
下面是如何将其转换为 DATE 和 TIMESTAMP WITH TIME ZONE 数据类型的示例。请注意，对于 DATE 类型，时区信息会丢失（在从 TIMESTAMP WITH TIME ZONE 转换中）：
declare
strDate VARCHAR2(32767);
tzDate TIMESTAMP WITH TIME ZONE;
dtDate DATE;
nTimezone NUMBER;
dtDate_GMT DATE;
begin
strDate := '2002-10-10T12:00:00-05:00';
dtDate := TO_TIMESTAMP_TZ(SUBSTR(strDate, 1, 10) ||
SUBSTR(strDate, 12, 8) || ' ' ||
SUBSTR(strDate, 20, 6), 'YYYY-MM-DDHH:MI:SS TZH:TZM');
tzDate := TO_TIMESTAMP_TZ(SUBSTR(strDate, 1, 10) ||
SUBSTR(strDate, 12, 8) || ' ' ||
SUBSTR(strDate, 20, 6), 'YYYY-MM-DDHH:MI:SS TZH:TZM');
nTimezone := TO_NUMBER(SUBSTR(strDate, 20, 3)) +
(TO_NUMBER(SUBSTR(strDate, 24, 2)) / 60);
dtDate_GMT := dtDate - ((INTERVAL '1' HOUR) * nTimezone);
dbms_output.put_Line('dtDate=' || dtDate);
dbms_output.put_Line('dtDate=' || TO_CHAR(dtDate, 'YYYY-MM-DD HH24:MI:SS'));
dbms_output.put_line('tzDate=' || tzDate);
dbms_output.put_line('tzDate=' || TO_CHAR(tzDate, 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
dbms_output.put_line('nTimezone=' || nTimezone);
dbms_output.put_Line('dtDate_GMT=' || TO_CHAR(dtDate_GMT, 'YYYY-MM-DD HH24:MI:SS'));
end;
Just for the fun of it I added some code to the example to pull the timezone out of the string, and then added the timezone to the local time to obtain GMT/UTC.
只是为了好玩，我在示例中添加了一些代码以从字符串中提取时区，然后将时区添加到本地时间以获得 GMT/UTC。
Share and enjoy.
分享和享受。
