oracle tz_offset 参数,无法根据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)。