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

專注于為中小企業(yè)提供成都網(wǎng)站設計、成都做網(wǎng)站服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)臺州免費做網(wǎng)站提供優(yōu)質的服務。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了數(shù)千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設實現(xiàn)規(guī)模擴充和轉變。

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

1. 如何查看和修改數(shù)據(jù)庫和session時區(qū)

Oracle中相關的時區(qū)大體可以分為兩類：數(shù)據(jù)庫時區(qū)和session時區(qū)?？梢酝ㄟ^以下方式獲得：

查看數(shù)據(jù)庫時區(qū)信息：

SQL> select dbtimezone from dual;

DBTIME
------
+08:00

查看session時區(qū)信息：

SQL> select sessiontimezone from dual;

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

Database的timezone 可以在創(chuàng)建數(shù)據(jù)庫的時候指定，如：

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

或者在數(shù)據(jù)庫創(chuàng)建之后通過alter database語句修改，但是只有重啟數(shù)據(jù)庫后有效：

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 數(shù)據(jù)類型相關！ 其實數(shù)據(jù)庫timezone只是一個計算的標尺，TIMESTAMP WITH LOCAL TIME ZONE數(shù)據(jù)類型從客戶端傳入數(shù)據(jù)庫后，轉為數(shù)據(jù)庫時區(qū)存入數(shù)據(jù)庫。在需要進行相關計算的時候，Oracle先把時間轉換為標準時間(UTC)，完成計算后再把結果轉換為數(shù)據(jù)庫時區(qū)的時間保存到數(shù)據(jù)庫。關于TIMESTAMP WITH LOCAL TIME ZONE數(shù)據(jù)類型的詳細信息，請參考隨后相關部分:)

2. 時區(qū)相關的數(shù)據(jù)類型

和時區(qū)相關的數(shù)據(jù)類型主要有：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類型的擴展，保留小數(shù)級別的秒，默認為小數(shù)點后６位。不保存時區(qū)和地區(qū)信息。

SQL> select localtimestamp from dual;

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

TIMESTAMP WITH TIME ZONE ：存儲帶時區(qū)信息的TIMESTAMP(以和UTC時間差或者地區(qū)信息的形式保存)。形式大致為：

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

TIMESTAMP WITH LOCAL TIME ZONE ：另一種不同類型的TIMESTAMP，和TIMESTAMP WITH TIME ZONE類型的區(qū)別在于：數(shù)據(jù)庫不保存時區(qū)相關信息，而是把客戶端輸入的時間轉換為基于database timezone的時間后存入數(shù)據(jù)庫(這也就是database tmiezone設置的意義所在，作為TIMESTAMP WITH LOCAL TIME ZONE類型的計算標尺)。當用戶請求此類型信息時，Oracle把數(shù)據(jù)轉換為用戶session的時區(qū)時間返回給用戶。所以Oracle建議把database timezone設置為標準時間UTC，這樣可以節(jié)省每次轉換所需要的開銷，提高性能。

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

操作DATE類型數(shù)據(jù) ：

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數(shù)據(jù)類型 ：

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: 第三條數(shù)據(jù)的時區(qū)信息丟失！

操作TIMESTAMP WITH TIME ZONE數(shù)據(jù)類型 ：

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: 第三條數(shù)據(jù)保存了時區(qū)信息！ 可以和上一個例子TIMESTAMP類型做一個對比。

操作TIMESTAMP WITH LOCAL TIME ZONE數(shù)據(jù)類型 ：

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: 插入的第三條數(shù)據(jù)指定為UTC-8時區(qū)的時間，然后存入數(shù)據(jù)庫后按照database timezone的時間保存，最后在客戶端請求的時候，轉換為客戶端時區(qū)的時間(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

可以看出，當客戶端時區(qū)改為UTC-5的時候，TIMESTAMP WITH LOCAL TIME ZONE數(shù)據(jù)類型的返回信息是會相應改變的。

在了解了相關數(shù)據(jù)類型后，那么我們該如何在它們之間做出選擇呢？

當你不需要保存時區(qū)/地區(qū)信息的時候，選擇使用TIMESTAMP數(shù)據(jù)類型，因為它一般需要7-11bytes的存儲空間，可以節(jié)省空間。

當你需要保存時區(qū)/地區(qū)信息的時候，請選擇使用TIMESTAMP WITH TIME ZONE數(shù)據(jù)類型。比如一個跨國銀行業(yè)務應用系統(tǒng)，需要精確紀錄每一筆交易的時間和地點(時區(qū))，在這種情況下就需要紀錄時區(qū)相關信息。因為需要紀錄時區(qū)相關信息，所以需要多一些的存儲空間，一般需要13bytes。

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

3. 時區(qū)相關的幾個函數(shù)

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的返回信息是數(shù)據(jù)庫所在操作系統(tǒng)的信息，和當前session的時區(qū)無關！

例：

數(shù)據(jù)庫時區(qū)為+08:00，當前session時區(qū)為-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時區(qū)改為+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時區(qū)的改變而改變的，其實從函數(shù)的命名就能看出(一組是system的，一組是current的~):D

總結： 由于這次case涉及到的東西就那么多，因此總結起來也沒有面面俱到，所有東西都包括。這里只是簡單的總結了怎么查看和修改數(shù)據(jù)庫/session時區(qū)，相關的data types和functions。還有諸如Interval Datatypes(存儲的是時間間隔), Daylight Saving Time(夏令時，我到現(xiàn)在還不是很清楚~)以及其他functions，parameters等等都沒有涉及。

關于time zone的系統(tǒng)介紹，請參考 Oracle Database Globalization Support Guide, Chapter 4 。還有其他的 官方文檔 和 metalink 都可以作為參考。

PS:小小case一則~

應用層用戶發(fā)現(xiàn)sysdate信息不對，本應為+09:00的時間，卻顯示為-05:00時區(qū)的時間，要求修改數(shù)據(jù)庫timezone。其實sysdate返回信息和數(shù)據(jù)庫timezone設置無關，遂去查看操作系統(tǒng)信息。發(fā)現(xiàn)果然是操作系統(tǒng)層設置就存在問題。但當時的問題是操作系統(tǒng)又不能隨便重啟，問題變得很棘手！

后來經過同事建議，設置了操作系的session信息：setenv TZ Japan。然后重啟了listener和database。之后所有經過listener連接到數(shù)據(jù)庫的用戶select sysdate from dual;的結果都是正確的信息，而沒有通過listener連接的用戶得到的則還是錯誤的信息，因為操作系統(tǒng)本身的時區(qū)并沒有更新。據(jù)說在session級別設置好時區(qū)信息后，只要重啟listener就足夠了，個人沒有試過，有興趣的可以嘗試下~

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

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

當前名稱：關于OracleTimezone的一點總結
瀏覽地址：http://chinadenli.net/article40/ppgeho.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、Google、網(wǎng)站建設、品牌網(wǎng)站設計、網(wǎng)站營銷、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)