關于Oracle Timezone的一點總結

原文：http://blog.itpub.net/9765498/viewspace-539881

背景描述： 如果需要支持一個國際化的應用，那么數據庫端的國際化特性的支持也就顯得尤其重要。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就足夠了，個人沒有試過，有興趣的可以嘗試下~

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

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