無法根據TZ_OFFSET的值進行資料訪問

今天碰到一個比較奇怪的現象，無法根據TZ_OFFSET函式的返回值，進行查詢。

 

 

TZ_OFFSET函式是Oracle根據時區名稱，返回時區與0時區相差的小時和分鐘數。

SQL> SELECT TZ_OFFSET('Asia/Shanghai') FROM DUAL;

TZ_OFFS
-------
+08:00

比如這個例子中，對於Asia/Shanghai這個時區，返回了+08:00，說明上海這是時區是東八區。

不過使用這個函式的時候也發現了一個奇怪的現象，比如再V$TIMEZONE_NAMES檢視中，包含了Oracle支援的所有時區名稱和時區簡稱，但是沒有辦法根據TZ_OFFSET的結果來反查這張表：

SQL> SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
  2  FROM V$TIMEZONE_NAMES
  3  WHERE TZ_OFFSET(TZNAME) = '+08:00';

未選定行

剛剛已經查詢到時區為+08:00的時區了，為什麼指定時區的OFFSET卻找不到呢，莫非其中的字元+或:的全形和半形出了問題。

將上面的查詢結果直接複製過去，問題依舊，仍然無法查詢到結果。

嘗試模糊查詢：

SQL> SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
  2  FROM V$TIMEZONE_NAMES
  3  WHERE TZ_OFFSET(TZNAME) LIKE '+08:00%';

TZNAME                                   TZABBREV                       TZ_OFFS
---------------------------------------- ------------------------------ -------
Asia/Chongqing                           LMT                            +08:00
Asia/Chongqing                           LONT                           +08:00
Asia/Chongqing                           CST                            +08:00
.
.
.
ROC                                      LMT                            +08:00
ROC                                      CST                            +08:00
ROC                                      CDT                            +08:00
Singapore                                LMT                            +08:00
Singapore                                SMT                            +08:00
Singapore                                MALT                           +08:00
Singapore                                JST                            +08:00
Singapore                                SGT                            +08:00

已選擇89行。

模糊匹配可以得到結果，而直接查詢卻無法得多結果，這說明TZ_OFFSET的返回值還包含了其他的字元。

觀察上面的結果也可以看到，根據字串型別的特點，+08:00後面應該還有1個不可見的字元，否則TZ_OFFSET這個列顯示的就不是TZ_OFFS而應該是TZ_OFF。

下面看看TZ_OFFSET返回結果的長度：

SQL> SELECT LENGTH(TZ_OFFSET('Asia/Shanghai')) FROM DUAL;

LENGTH(TZ_OFFSET('ASIA/SHANGHAI'))
----------------------------------
                                 7

果然這裡包含了一個不可見的字元，透過DUMP來看看到底是什麼：

SQL> SELECT DUMP(TZ_OFFSET('Asia/Shanghai'), 16)
  2  FROM DUAL;

DUMP(TZ_OFFSET('ASIA/SHANGHAI'),16)
-----------------------------------------------------------
Typ=1 Len=7: 2b,30,38,3a,30,30,0

居然是0，注意這個0不是數值0，而是ASCII碼0值，也就是C語句中字串的結束符“\0”。

在Oracle中這個值可以用CHR(0)來表示，於是上面的查詢可以改為：

SQL> SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME)
  2  FROM V$TIMEZONE_NAMES
  3  WHERE TZ_OFFSET(TZNAME) = '+08:00' || CHR(0);

TZNAME                                   TZABBREV                       TZ_OFFS
---------------------------------------- ------------------------------ -------
Asia/Chongqing                           LMT                            +08:00
Asia/Chongqing                           LONT                           +08:00
Asia/Chongqing                           CST                            +08:00
Asia/Chongqing                           CDT                            +08:00
.
.
.
Singapore                                MALT                           +08:00
Singapore                                JST                            +08:00
Singapore                                SGT                            +08:00

已選擇89行。

這裡來看一看TIMESTAMP型別的DUMP結果：

SQL> SELECT DUMP(TIMESTAMP '2009-3-8 20:57:32.23 +08:00', 16)
  2  FROM DUAL;

DUMP(TIMESTAMP'2009-3-820:57:32.23+08:00',16)
-------------------------------------------------------------
Typ=188 Len=20: d9,7,3,8,c,39,20,0,80,85,b5,d,8,0,5,0,0,0,0,0

無論是在時區8的後面，還是在DUMP結果的最後，都有CHR(0)的存在，可能正式由於TIMESTAMP型別的特點，導致了TZ_OFFSET返回結果後面新增了一個CHR(0)。