Mac De PL/SQL RSS Reader #12 Tweet

PL/SQL RSS Readerの続きです。では、早速、rfc822_dateStrToTimestampLTZ()のテストから、

GLASSCATFISH> !cat rfc822_func_test.sql
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 GMT') from dual;
select rfc822_datestrtotimestampltz('07 Sep 2002 00:00:01 GMT') from dual;
select rfc822_datestrtotimestampltz('07 Sep 02 00:00:01 GMT') from dual;
select rfc822_datestrtotimestampltz('07 Sep 02 00:00 GMT') from dual;
select rfc822_datestrtotimestampltz('07 Sep 02') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 02 00:00:01 +0900') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 02 00:00:01 -0900') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 EST') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 CST') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 MST') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 PST') from dual;
select rfc822_datestrtotimestampltz('Sun, 8 Oct 2006 07:00:00 +0900') from dual;
select rfc822_datestrtotimestampltz('Sat, 7 Oct 2006 22:00:00 GMT') from dual;
PROMPT 夏時間の確認
select rfc822_datestrtotimestampltz('7 Oct 2006 18:00:00 EST') from dual;
select rfc822_datestrtotimestampltz('7 Oct 2006 18:00:00 -0500') from dual;
select rfc822_datestrtotimestampltz('7 Oct 2006 18:00:00 -0400') from dual;
select rfc822_datestrtotimestampltz('31 Oct 2006 22:00:00 EST') from dual;
select rfc822_datestrtotimestampltz('31 Oct 2006 22:00:00 -0500') from dual;
select rfc822_datestrtotimestampltz('31 Oct 2006 22:00:00 -0400') from dual;
PROMPT オラクルのタイムゾーンリージョンに無いコード
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 EDT') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 CDT') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 MDT') from dual;
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 PDT') from dual;
--
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 UT') from dual;


GLASSCATFISH> @rfc822_func_test

RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP200200:00:01GMT')
---------------------------------------------------------------------------
02-09-07 09:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('07SEP200200:00:01GMT')
---------------------------------------------------------------------------
02-09-07 09:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('07SEP0200:00:01GMT')
---------------------------------------------------------------------------
02-09-07 09:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('07SEP0200:00GMT')
---------------------------------------------------------------------------
02-09-07 09:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('07SEP02')
---------------------------------------------------------------------------
02-09-07 00:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP0200:00:01+0900')
---------------------------------------------------------------------------
02-09-07 00:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP0200:00:01-0900')
---------------------------------------------------------------------------
02-09-07 18:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP200200:00:01EST')
---------------------------------------------------------------------------
02-09-07 13:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP200200:00:01CST')
---------------------------------------------------------------------------
02-09-07 14:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP200200:00:01MST')
---------------------------------------------------------------------------
02-09-07 15:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,07SEP200200:00:01PST')
---------------------------------------------------------------------------
02-09-07 16:00:01.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SUN,8OCT200607:00:00+0900')
---------------------------------------------------------------------------
06-10-08 07:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('SAT,7OCT200622:00:00GMT')
---------------------------------------------------------------------------
06-10-08 07:00:00.000000000

夏時間の確認

RFC822_DATESTRTOTIMESTAMPLTZ('7OCT200618:00:00EST')
---------------------------------------------------------------------------
06-10-08 07:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('7OCT200618:00:00-0500')
---------------------------------------------------------------------------
06-10-08 08:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('7OCT200618:00:00-0400')
---------------------------------------------------------------------------
06-10-08 07:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('31OCT200622:00:00EST')
---------------------------------------------------------------------------
06-11-01 12:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('31OCT200622:00:00-0500')
---------------------------------------------------------------------------
06-11-01 12:00:00.000000000


RFC822_DATESTRTOTIMESTAMPLTZ('31OCT200622:00:00-0400')
---------------------------------------------------------------------------
06-11-01 11:00:00.000000000

オラクルのタイムゾーンリージョンに無いコード
select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 EDT') from dual
       *
行1でエラーが発生しました。:
ORA-20001: RFC822_DateStrToTimeStampLTZ():ORA-01882:
タイムゾーンのリージョンが見つかりません。
ORA-06512: "GLASSCATFISH.RFC822_DATESTRTOTIMESTAMPLTZ", 行48


select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 CDT') from dual
       *
行1でエラーが発生しました。:
ORA-20001: RFC822_DateStrToTimeStampLTZ():ORA-01882:
タイムゾーンのリージョンが見つかりません。
ORA-06512: "GLASSCATFISH.RFC822_DATESTRTOTIMESTAMPLTZ", 行48v

select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 MDT') from dual
       *
行1でエラーが発生しました。:
ORA-20001: RFC822_DateStrToTimeStampLTZ():ORA-01882:
タイムゾーンのリージョンが見つかりません。
ORA-06512: "GLASSCATFISH.RFC822_DATESTRTOTIMESTAMPLTZ", 行48


select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 PDT') from dual
       *
行1でエラーが発生しました。:
ORA-20001: RFC822_DateStrToTimeStampLTZ():ORA-01882:
タイムゾーンのリージョンが見つかりません。
ORA-06512: "GLASSCATFISH.RFC822_DATESTRTOTIMESTAMPLTZ", 行48


select rfc822_datestrtotimestampltz('Sat, 07 Sep 2002 00:00:01 UT') from dual
       *
行1でエラーが発生しました。:
ORA-20001: RFC822_DateStrToTimeStampLTZ():ORA-01882:
タイムゾーンのリージョンが見つかりません。
ORA-06512: "GLASSCATFISH.RFC822_DATESTRTOTIMESTAMPLTZ", 行48


GLASSCATFISH> 

と、このような感じになる。夏時間は自動計算されているようだ。
ということは・・・、"EDT"/"CDT"/"MDT"/"PDT"は単純に"EST"/"CST"/"MST"/"PST"に置換するだけで対応できるかも！？

テストのログが長くなってしまったので、今日はここまで。ストアドファンクションのソースコードは次回。

---

いろいろと行事があって、少々疲れ気味。。。やはりこんなときは、Smooth Jazz....
ということで、今日も　Internet Radio　SmoothJazz.com