我需要将一个日期从以下格式转换：
2002-10-10T12:00:00-05:00 (XML中定义的xs:dateTime)
转换成Oracle日期。
我通常在PL/SQL中使用以下语句：to_date('date here', 'yyyymmdd')，有没有一种方法可以在保留时区信息的同时进行转换？
谢谢
3个回答
9
一个简短的回答：
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.
问候， 罗布。
- Rob van Wijk
6
Oracle的日期类型不包含时区信息。您需要使用TIMESTAMP数据类型代替。
它的使用方式如下：
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中不是一个有效的格式。我确定有一种解决方法，但目前我还不知道。
好吧，一个解决方法是应用SUBSTR()函数来分开时间戳的两个部分，就像Bob所展示的那样。但应该有更加优雅的方法。
虽然可能不太优雅，但由于它是一个字符串，我们可以使用替换函数来摆脱烦人的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方面投入了大量的精力，但令人不爽的是，没有一个更整洁的解决方案。
"我不明白你是如何得到-05:00的。"
在我的原始示例中，我使用了一个格式掩码'YYYY-MM-DD HH24:MI:SS-TZH:TZM'。它将时区中的-解释为分隔符而不是减号。因此返回值是+05:00。我已经纠正了我的代码示例以删除最后一个破折号。现在时区被正确地呈现为-05:00。对任何困惑请见谅。
- APC
2
谢谢！但是当我执行以下语句时：select to_timestamp_tz(translate('2003-10-10T12:00:00-05:00', 'T', ' '), 'YYYY-MM-DD HH24:MI:SS-TZH:TZM') from dual;我得到的结果是 10-OCT-03 12.00.00.000000000 PM +05:00（我应该得到 -05:00 而不是 +05:00），怎么办？ - user412045
2如何处理烦人的T，将其放在双引号中：'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM' - juanitogan
1
这是一个如何将其转换为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;
为了好玩，我在示例中添加了一些代码来提取字符串中的时区，然后将时区添加到本地时间以获得GMT / UTC。
分享并享受。
- Bob Jarvis - Слава Україні
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
2002-10-10T12:00:00-05:00转换成20021010吗？您所说的“保留时区信息”是什么意思？ - user357812