本文介绍了将 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."

在我的原始示例中,我使用 '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.

这篇关于将 XSD 日期 xs:dateTime 转换为 Oracle 日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-30 08:00