将 XSD 日期 xs:dateTime 转换为 Oracle 日期
我需要从这种格式转换日期:
I need to convert a date from this format:
2002-10-10T12:00:00-05:00(XML 中定义的 xs:dateTime)
2002-10-10T12:00:00-05:00 (xs:dateTime as defined in XML)
到 Oracle 日期.
我习惯在 PL/SQL 中使用它:to_date('date here', 'yyyymmdd')，有没有办法在保留时区信息的同时转换它?
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?
谢谢
推荐答案
Oracle 日期没有时区信息.您需要改用 TIMESTAMP 数据类型.
Oracle dates don't have timezone information. You'll need to use a TIMESTAMP datatype instead.
它的工作原理是这样的:
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>
注意，XSD 表示法中的 T 存在一个棘手的问题.这会引发 ORA-01858 异常，因为它不是 Oracle 中的有效格式.我确定有一种解决方法，但它目前使我无法理解.
Note, there is the tricky issue of the T in the XSD notation. That hurls a ORA-01858 exception, because it's not a valid format in Oracle. I'm sure there is a workaround, but it currently escapes me.
嗯，一种解决方法是应用 SUBSTR() 函数 s 来拆分时间戳的两个部分，如 Bob 所示.但应该有更优雅的方式.
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.
它可能不符合优雅"的条件，但由于它是一个字符串，我们可以使用替换函数来摆脱烦人的 T:
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:
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>
但考虑到 Oracle 为 XMLDB 付出的所有努力，没有一个更整洁的解决方案是相当烦人的.
But given all the effort Oracle have put into XMLDB it is rather annoying that there isn't a tidier solution.
我不明白你是怎么得到的-05:00."
"I dont understand how you get -05:00."
在我的原始示例中，我使用 'YYYY-MM-DD HH24:MI:SS-TZH:TZM' 格式掩码.这会将时区中的 - 解释为分隔符而不是减号.因此它返回+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 not a 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.
相关文章