Oracle timezone的相關知識

crystal_ocean發表於2014-02-12
Oracle
目前我工作中生產環境都是面向國際化的應用，會遇到有些關於時區的問題，在此做下總結。

oracle中的時區大體分為兩類：資料庫時區、session 時區。我們可以通過以下方式來檢視：

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


SQL> select sessiontimezone from dual;

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

資料庫時區可以在建庫的時候指定，或者在資料庫建成後使用 alter database  set time_zone='+8:00' 進行修改，修改後重啟庫才能生效。
session時區使用 alter session set time_zone=‘+8:00’ 進行修改。

在這裡我們不得不提的是和時區相關的資料型別，因為有這些資料型別，時區的設定才有意義。
與時區相關的資料型別：DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE 等

DATE：儲存的是日期+時間，精確到秒級，不儲存時區和地區資訊。
SQL> select sysdate from dual;

SYSDATE
-------------------
2014-02-12 01:12:18

TIMESTAMP:DATE型別的擴充套件，保留小數級別的秒，預設為小數點後６位，不儲存時區和地區資訊。
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-FEB-14 01.14.12.945256 AM

SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF';       ----修改TIMESTAMP資料型別的格式

Session altered.

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
2014-02-12 01:28:31.652888


TIMESTAMP WITH TIME ZONE:儲存帶時區資訊的TIMESTAMP，此型別的資料在儲存到資料庫時帶有當前客戶端的session timezone，無論在什麼時區檢視這些資料的時候，資料都不會隨時區的不同而變化。

SQL> create table t1 (id number,time timestamp with time zone);         ----建立t1表，其中time 列的資料型別是timestamp with time zone

Table created.

SQL> select sessiontimezone from dual;                                                 -----當前客戶端的session timezone 是 -8:00
 
SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00

SQL> insert into t1 values(1,timestamp '2014-02-12 02:00:00');          ----向t1表中插入一條資料  
1 row created.

SQL> select * from t1;                                              ----檢視t1表，其中time列帶時區顯示，並且時區為資料被插入時的session timezone

        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

SQL> alter session set time_zone='-6:00';                                    ----修改當前客戶端的session timezone為 -6:00

Session altered.

SQL> select * from t1;                                                                ----再次檢視t1表，其中time列資料無變化。

        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00


TIMESTAMP WITH LOCAL TIME ZONE：另一種不同型別的TIMESTAMP，資料庫不儲存時區相關資訊，而是把客戶端輸入的時間轉換為基於
database timezone的時間後存入資料庫，(這也就是database tmiezone設定的意義所在，作為TIMESTAMP WITH LOCAL TIME ZONE型別的計算標尺)。
當使用者請求此型別資訊時，Oracle把資料轉換為使用者session的時區時間返回給使用者。

SQL> create table t2(id number,time timestamp with local time zone);        ----- 建立t2表，其中time列為TIMESTAMP WITH LOCAL TIME ZONE

Table created.

SQL> insert into t2 values(1,timestamp '2014-02-12 02:10:00 -8:00');      

1 row created.                                       -----在t2表插入資料指定時區為-8:00，實際在儲存到資料庫時轉化為基於database timezone的時間儲存

SQL> select sessiontimezone from dual;              -----當前客戶端的session timezone 為 -6:00

SESSIONTIMEZONE
---------------------------------------------------------------------------
-06:00

SQL> select * from t2;                      -----檢視時oracle將資料轉換成當前客戶端session timezone的時間

        ID      TIME
----------   ---------------------------------------------------------------------------
         1       2014-02-12 04:10:00.000000


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

不需要儲存時區/地區資訊的時候，選擇使用TIMESTAMP資料型別

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

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












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

相關文章