關於Oracle Timezone的一點總結

背景描述：如果需要支援一個國際化的應用，那麼資料庫端的國際化特性的支援也就顯得尤其重要。Oracle中有很多特性支援國際化，如字符集、時區等等。如果相關引數設定不當，或者由於對相關特性不夠了解，以至於在設計階段沒有考慮完全，那麼肯定會對應用造成一定的損失。偶前不久也遇到了time zone相關的問題，所以在此結合遇到的問題，對時區問題作一個小小的總結。

1. 如何檢視和修改資料庫和session時區

Oracle中相關的時區大體可以分為兩類：資料庫時區和session時區。可以通過以下方式獲得：

檢視資料庫時區資訊：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

檢視session時區資訊：

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

Database的timezone可以在建立資料庫的時候指定，如：

CREATE DATABASE db01
...
SET TIME_ZONE='+08:00';

或者在資料庫建立之後通過alter database語句修改，但是隻有重啟資料庫後有效：

ALTER DATABASE SET TIME_ZONE='+08:00';

session的timezone可以簡單通過alter session語句修改：

ALTER SESSION SET TIME_ZONE='+08:00';

Note:Database Time Zone只和TIMESTAMP WITH LOCAL TIME ZONE資料型別相關！其實資料庫timezone只是一個計算的標尺，TIMESTAMP WITH LOCAL TIME ZONE資料型別從客戶端傳入資料庫後，轉為資料庫時區存入資料庫。在需要進行相關計算的時候，Oracle先把時間轉換為標準時間(UTC)，完成計算後再把結果轉換為資料庫時區的時間儲存到資料庫。關於TIMESTAMP WITH LOCAL TIME ZONE資料型別的詳細資訊，請參考隨後相關部分:)

2. 時區相關的資料型別

和時區相關的資料型別主要有：DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE。粗略介紹如下：

DATE：儲存日期和時間資訊，精確到秒。

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select to_date('2009-01-12 13:24:33','YYYY-MM-DD HH24:MI:SS') from dual;

TO_DATE('2009-01-12
-------------------
2009-01-12 13:24:33

TIMESTAMP：DATE型別的擴充套件，保留小數級別的秒，預設為小數點後６位。不儲存時區和地區資訊。

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07.21.37.984000 PM

TIMESTAMP WITH TIME ZONE：儲存帶時區資訊的TIMESTAMP(以和UTC時間差或者地區資訊的形式儲存)。形式大致為：

TIMESTAMP '2009-01-12 8:00:00 +8:00'

TIMESTAMP WITH LOCAL TIME ZONE：另一種不同型別的TIMESTAMP，和TIMESTAMP WITH TIME ZONE型別的區別在於：資料庫不儲存時區相關資訊，而是把客戶端輸入的時間轉換為基於database timezone的時間後存入資料庫(這也就是database tmiezone設定的意義所在，作為TIMESTAMP WITH LOCAL TIME ZONE型別的計算標尺)。當使用者請求此型別資訊時，Oracle把資料轉換為使用者session的時區時間返回給使用者。所以Oracle建議把database timezone設定為標準時間UTC，這樣可以節省每次轉換所需要的開銷，提高效能。

下面是針對以上幾種型別所做的實驗：

操作DATE型別資料：

SQL> INSERT INTO table_dt VALUES(1,DATE '2009-01-01');

1 row created.

SQL> INSERT INTO table_dt VALUES(2,TIMESTAMP '2009-01-01 00:00:00 Asia/Hong_Kong');

1 row created.

SQL>  INSERT INTO table_dt VALUES(3,TO_DATE('01-JAN-2009','DD-MON-YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_dt;

      C_ID C_DT
---------- -------------------
         1 2009-01-01 00:00:00
         2 2009-01-01 00:00:00
         3 2009-01-01 00:00:00

操作TIMESTAMP資料型別：

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

Session altered.

SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);

Table created.

SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2009 2:00:00');

1 row created.

SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2009-01-01 2:00:00 -08:00');

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 120
SQL> select * from table_ts;

      C_ID C_TS
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 02:00:00.000000
         2 01-JAN-09 02:00:00.000000
         3 01-JAN-09 02:00:00.000000

Note: 第三條資料的時區資訊丟失！

操作TIMESTAMP WITH TIME ZONE資料型別：

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

Session altered.

SQL> ALTER SESSION SET TIME_ZONE='-7:00';

Session altered.

SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);

Table created.

SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2009 2:00:00 AM -07:00');

1 row created.

SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2009-01-01 2:00:00 -8:00');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tstz;

      C_ID C_TSTZ
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 02:00:00.000000 AM -07:00
         2 01-JAN-09 02:00:00.000000 AM -07:00
         3 01-JAN-09 02:00:00.000000 AM -08:00
Note: 第三條資料儲存了時區資訊！ 可以和上一個例子TIMESTAMP型別做一個對比。

操作TIMESTAMP WITH LOCAL TIME ZONE資料型別：

SQL> ALTER SESSION SET TIME_ZONE='-07:00';

Session altered.

SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2009 2:00:00');

1 row created.

SQL>  INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2009-01-01 2:00:00');

1 row created.

SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2009-01-01 2:00:00 -08:00');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table_tsltz;

      C_ID C_TSLTZ
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 02:00:00.000000
         2 01-JAN-09 02:00:00.000000
         3 01-JAN-09 03:00:00.000000

Note: 插入的第三條資料指定為UTC-8時區的時間，然後存入資料庫後按照database timezone的時間儲存，最後在客戶端請求的時候，轉換為客戶端時區的時間(UTC-7)返回！可以參考以下簡單實驗：

SQL> ALTER SESSION SET TIME_ZONE='-05:00';

Session altered.

SQL> select * from table_tsltz;

      C_ID C_TSLTZ
---------- ---------------------------------------------------------------------------
         1 01-JAN-09 04:00:00.000000
         2 01-JAN-09 04:00:00.000000
         3 01-JAN-09 05:00:00.000000

可以看出，當客戶端時區改為UTC-5的時候，TIMESTAMP WITH LOCAL TIME ZONE資料型別的返回資訊是會相應改變的。

在瞭解了相關資料型別後，那麼我們該如何在它們之間做出選擇呢？

當你不需要儲存時區/地區資訊的時候，選擇使用TIMESTAMP資料型別，因為它一般需要7-11bytes的儲存空間，可以節省空間。

當你需要儲存時區/地區資訊的時候，請選擇使用TIMESTAMP WITH TIME ZONE資料型別。比如一個跨國銀行業務應用系統，需要精確紀錄每一筆交易的時間和地點(時區)，在這種情況下就需要紀錄時區相關資訊。因為需要紀錄時區相關資訊，所以需要多一些的儲存空間，一般需要13bytes。

當你並不關心操作發生的具體地點，而只是關心操作是在你當前時區的幾點發生的時候，選擇使用TIMESTAMP WITH LOCAL TIME ZONE。比如一個全球統一的change control system。使用者可能只關心某某操作是在我的時間幾點發生的(比如中國使用者看到的是北京時間8:00am，而倫敦的使用者看到的是0:00am)。記住，此類行不儲存時區/地區資訊，因此如果需要儲存相關資訊的要慎重！

3. 時區相關的幾個函式

DBTIMEZONE -- Returns the value of the database time zone. The value is a time zone offset or a time zone region name.

SESSIONTIMEZONE -- Returns the value of the current session's time zone.

CURRENT_DATE -- Returns the current date in the session time zone in a value in the Gregorian calendar, of the DATE datatype.
 
CURRENT_TIMESTAMP -- Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE value.

SYSDATE -- Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.
 
SYSTIMESTAMP -- Returns the system date, including fractional seconds and time zone of the system on which the database resides.
 
Note: SYSDATE和SYSTIMESTAMP的返回資訊是資料庫所在作業系統的資訊，和當前session的時區無關！

例：

資料庫時區為+08:00，當前session時區為-05:00時：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2009-01-12 06:18:24

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 06:18:36.625000 AM -05:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2009-01-12 19:18:42

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07:18:52.921000 PM +08:00

SQL>

把當前session時區改為+09:00以後：

SQL> alter session set time_zone='+09:00';

Session altered.

SQL>
SQL> select dbtimezone from dual;

DBTIME
------
+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+09:00

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2009-01-12 20:19:54

SQL>
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 08:20:07.218000 PM +09:00

SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2009-01-12 19:20:24

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-JAN-09 07:20:30.921000 PM +08:00

SQL>

從以上例子可以看出，SYSDATE和SYSTIMESTAMP的返回結果是不隨SESSION時區的改變而改變的，其實從函式的命名就能看出(一組是system的，一組是current的~):D

總結：由於這次case涉及到的東西就那麼多，因此總結起來也沒有面面俱到，所有東西都包括。這裡只是簡單的總結了怎麼檢視和修改資料庫/session時區，相關的data types和functions。還有諸如Interval Datatypes(儲存的是時間間隔), Daylight Saving Time(夏令時，我到現在還不是很清楚~)以及其他functions，parameters等等都沒有涉及。

關於time zone的系統介紹，請參考Oracle Database Globalization Support Guide, Chapter 4。還有其他的官方文件和metalink都可以作為參考。

PS:小小case一則~

應用層使用者發現sysdate資訊不對，本應為+09:00的時間，卻顯示為-05:00時區的時間，要求修改資料庫timezone。其實sysdate返回資訊和資料庫timezone設定無關，遂去檢視作業系統資訊。發現果然是作業系統層設定就存在問題。但當時的問題是作業系統又不能隨便重啟，問題變得很棘手！

後來經過同事建議，設定了操作系的session資訊：setenv TZ Japan。然後重啟了listener和database。之後所有經過listener連線到資料庫的使用者select sysdate from dual;的結果都是正確的資訊，而沒有通過listener連線的使用者得到的則還是錯誤的資訊，因為作業系統本身的時區並沒有更新。據說在session級別設定好時區資訊後，只要重啟listener就足夠了，個人沒有試過，有興趣的可以嘗試下~

問題的根本解決方法還是要修改作業系統的時區設定，但有個臨時的解決方案也是不錯的了:)

還有一些關於字符集、地區等國際化特性的總結，以後整理下慢慢都發出來吧...:)
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27009266/viewspace-1147550/，如需轉載，請註明出處，否則將追究法律責任。