oracle、mysql時區設定對timestamp的不同影響

標籤：

因最近國際去Oracle上MySQL，這就不可避免的涉及到時區和timestamp問題。做一下實驗，總結一下。

 

Oracle

首先看下oracle concepts對timestamp的定義：

 

The TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. TheTIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for preserving local time zone information.

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that is sensitive to time zone information. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user‘s local session time zone. This data type is useful for date information that is always to be displayed in the time zone of the client system in a two-tier application.

 

• TIMESTAMP是對date的更高精度的一種儲存，但它不儲存時區資訊，即不受DBTIMEZONE影響

• TIMESTAMP WITH TIME ZONE儲存用戶端的時區資訊，所以也不受DBTIMEZONE影響

• TIMESTAMP WITH LOCAL TIME ZONE類型資料不會儲存用戶端的時區資訊，它根據資料庫時區對用戶端發來的時間進行轉換，基於統一的資料庫時區儲存時間資訊，如果使用者沒有指定時區資訊同TIMESTAMP WITH TIME ZONE一樣預設採用會話時區。把用戶端輸入的時間轉換為基於database timezone的時間後存入資料庫(這也就是database tmiezone設定的意義所在，作為TIMESTAMP WITH LOCAL TIME ZONE類型的計算尺規)。當使用者查看該類型資料時，伺服器根據會話所屬時區對儲存的時間資料進行轉換，不同時區的會話將返回不同的時間資料。所以Oracle建議把database timezone設定為標準時間UTC，這樣可以節省每次轉換所需要的開銷，提高效能。

 

v$nls_parameters表不僅存了資料庫的字元集資訊，還有關於timestamp和timestamp with local time zone的顯示格式：

SQL> select * from v$nls_parameters where parameter in (‘NLS_TIMESTAMP_FORMAT‘,‘NLS_TIMESTAMP_TZ_FORMAT‘);

PARAMETER                      VALUE

------------------------------ ------------------------------

NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR

 

看完定義，我們直接用實驗更直觀地看出它們的不同。

$ date -R

Sun, 24 Apr 2016 13:50:32 +0800

 

SQL>select dbtimezone,sessiontimezone from dual;

DBTIMEZONE           SESSIONTIMEZONE

-------------------- --------------------

+08:00               +08:00

 

我們進行插入資料的實驗

SQL>create table timezone_test(t0 timestamp,t1 timestamp with time zone,t2 timestamp with local time zone);

Table created.

 

SQL>insert into timezone_test select current_timestamp,current_timestamp,current_timestamp from dual;

1 row created.

 

SQL>select * from timezone_test;

T0                             T1                                   T2

------------------------------ ------------------------------------ ------------------------------

24-APR-16 02.35.03.613433 PM   24-APR-16 02.35.03.613433 PM +08:00  24-APR-16 02.35.03.613433 PM

 

SQL>alter session set time_zone=‘-2:00‘;

Session altered.

 

SQL>select * from timezone_test;

 

T0                             T1                                   T2

------------------------------ ------------------------------------ ------------------------------

24-APR-16 02.35.03.613433 PM   24-APR-16 02.35.03.613433 PM +08:00  24-APR-16 04.35.03.613433 AM

 

留意到T0和T1都是不變的。T2，即timestamp with local time zone所輸出的值發生了變化。我們這時從-2:00的另一個db用sqlplus串連，效果也是一樣的：

SQL>select dbtimezone,sessiontimezone from dual;

DBTIMEZONE           SESSIONTIMEZONE

-------------------- --------------------

+08:00               -02:00

 

SQL>insert into timezone_test select current_timestamp,current_timestamp,current_timestamp from dual;

1 row created.

 

SQL>select * from timezone_test;

T0                             T1                                   T2

------------------------------ ------------------------------------ ------------------------------

24-APR-16 02.35.03.613433 PM   24-APR-16 02.35.03.613433 PM +08:00  24-APR-16 04.35.03.613433 AM

24-APR-16 05.03.35.050304 AM   24-APR-16 05.03.35.050304 AM -02:00  24-APR-16 05.03.35.050304 AM

 

T2為(-2)-(+8)=-10時差

 

SQL>alter session set time_zone = dbtimezone;

Session altered.

 

SQL>select * from timezone_test;

T0                             T1                                   T2

------------------------------ ------------------------------------ ------------------------------

24-APR-16 02.35.03.613433 PM   24-APR-16 02.35.03.613433 PM +08:00  24-APR-16 02.35.03.613433 PM

24-APR-16 05.03.35.050304 AM   24-APR-16 05.03.35.050304 AM -02:00  24-APR-16 03.03.35.050304 PM

 

當dbtimezone與sessiontimezone不同時，插入資料。T0和T1即保留了插入時的時間字串資訊，不會改變，而T2則回到了我們真正插入的時間，即下午3點03分。

 

SQL>select dbtimezone,sessiontimezone from dual;

DBTIMEZONE           SESSIONTIMEZONE

-------------------- --------------------

+08:00               -02:00

 

SQL>insert into timezone_test select timestamp ‘2016-04-24 15:14:00 +3:00‘,timestamp ‘2016-04-24 15:14:00 +3:00‘,timestamp ‘2016-04-24 15:14:00 +3:00‘ from dual;

1 row created.

 

SQL>select * from timezone_test;

T0                             T1                                   T2

------------------------------ ------------------------------------ ------------------------------

24-APR-16 02.35.03.613433 PM   24-APR-16 02.35.03.613433 PM +08:00  24-APR-16 02.35.03.613433 PM

24-APR-16 05.03.35.050304 AM   24-APR-16 05.03.35.050304 AM -02:00  24-APR-16 03.03.35.050304 PM

24-APR-16 03.14.00.000000 PM   24-APR-16 03.14.00.000000 PM +03:00  24-APR-16 08.14.00.000000 PM

 

我們留意到，T0和T1都是我們插入的timestamp字串中的時間，而T2的時間則已經是(+3) - (-2) = 5，即已經進行了轉化。 

 

SQL>alter session set time_zone=‘+3:00‘;

Session altered.

SQL>select * from timezone_test;

T0                             T1                                   T2

------------------------------ ------------------------------------ ------------------------------

24-APR-16 02.35.03.613433 PM   24-APR-16 02.35.03.613433 PM +08:00  24-APR-16 09.35.03.613433 AM

24-APR-16 05.03.35.050304 AM   24-APR-16 05.03.35.050304 AM -02:00  24-APR-16 10.03.35.050304 AM

24-APR-16 03.14.00.000000 PM   24-APR-16 03.14.00.000000 PM +03:00  24-APR-16 03.14.00.000000 PM

 

而我們把時區再設定成我們插入時指定的+3時區時，即顯示出現的時間即是我們插入時的時間字串的值了。

 

所以，在Oracle中，TIMESTAMP WITH LOCAL TIME ZONE會隨著使用者所在時區(SESSIONTIMEZONE)而變化，而TIMESTAMP WITH TIME ZONE則不隨使用者所在時區的變化而變，簡單的說，這兩個時間類型的參照時間不同，一個是參照使用者的時區，一個是參照資料庫的時區。

timestamp with time zone則要加上時區，插入資料時插的什麼時區就顯示什麼時區，不會改變為別的或資料庫所在時區，或查詢人所在地的時區。插入資料時，如果寫時區，那麼顯示的時候以插入時候的時區顯示出來，而不是資料庫所在時區，或查詢人所在地的時區的時間；並且也不會轉換這個時間。

而timestamp with local time zone 就是顯示的時候不加後面的時區如+8:00。會存在轉換的問題。插入資料的時候帶時區就會轉換會資料庫所在時區，或查詢人所在地的時區來顯示資料。插入的時候不帶時區，則認為跟資料庫所在的時區是一樣，這樣查詢時的所在地如果與資料庫一樣的時區，則時間不變，如果不一致，則還要轉換為查詢所在的時區的時間。

 

但是，如果建庫時，時間設定錯誤或者是將來要改變時區時，表中的值會不會變化呢？由於我沒測試環境，因此引用官方的回答：

對於time zone資料類型的資料，即使你更新了資料庫時區，原資料也不會進行對應調整，只能你匯出資料，然後調整資料庫時區，再把未經處理資料匯入即可。所以，一般情況下，一定不要調整資料庫時區。官方建議資料庫時間採用UTC，因為這種時區效能好。如果沒有顯式指定資料庫時區，資料庫會使用作業系統的時區，但是如果作業系統時區不是一個合理的資料庫時區，資料庫則會使用預設的時區UTC，且UTC的取值範圍為-12:00 to +14:00。等等，為什麼會有+14？百度一下基里巴斯。這是一個神奇的網站國度。

 

 

MySQL

看下MySQL 5.6 Reference Manual對timestamp的定義

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01‘ UTC to ‘2038-01-19 03:14:07‘ UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME .) By default, the current time zone for each connection is the server‘s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. 

 

我們直接看下mysql中date和timestamp的不同：

 

DATETIME

1.8個位元組儲存（8 bytes storage）

2.實際格式儲存（Just stores what you have stored and retrieves the same thing which you have stored.）

3.與時區不轉換（It has nothing to deal with the TIMEZONE and Conversion.）

 

TIMESTAMP

1.4個位元組儲存（Time stamp value is stored in 4 bytes）

2.值以UTC格式儲存（ it stores the number of milliseconds）

3.時區轉化，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區。

 

mysql的timestamp沒有oracle複雜，直接實驗一下。

 

$date -R

Sun, 24 Apr 2016 05:50:02 -0700

 

[email protected] 05:50:16>show variables like ‘%time_zone%‘;

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| system_time_zone | PDT    |

| time_zone        | SYSTEM |

+------------------+--------+

 

[email protected] 05:50:14>create table timezone_test (

    ->   t1 datetime default null,

    ->   t2 timestamp not null default current_timestamp on update current_timestamp);

 

[email protected] 05:52:11>select * from timezone_test;

+---------------------+---------------------+

| t1                  | t2                  |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 05:52:11 |

+---------------------+---------------------+

 

[email protected] 05:52:21>set time_zone=‘+8:00‘;

 

[email protected] 05:52:47>select * from timezone_test;

+---------------------+---------------------+

| t1                  | t2                  |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 20:52:11 |

+---------------------+---------------------+

 

[email protected] 05:52:53>insert into timezone_test values(current_timestamp,current_timestamp);

 

[email protected] 05:52:59>select * from timezone_test;

+---------------------+---------------------+

| t1                  | t2                  |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 20:52:11 |

| 2016-04-24 20:55:04 | 2016-04-24 20:55:04 |

+---------------------+---------------------+

 

[email protected] 05:55:04>set time_zone=‘-7:00‘;

 

[email protected] 05:55:12>show variables like ‘%time_zone%‘;

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| system_time_zone | PDT    |

| time_zone        | SYSTEM |

+------------------+--------+

 

[email protected] 05:55:22>select * from timezone_test;

+---------------------+---------------------+

| t1                  | t2                  |

+---------------------+---------------------+

| 2016-04-24 05:52:11 | 2016-04-24 05:52:11 |

| 2016-04-24 20:55:04 | 2016-04-24 05:55:04 |

+---------------------+---------------------+

 

 

而在剛才建立表時timestamp列時用的default值，有下面的結論，有興趣可以做實驗下：

• 在建立新記錄和修改現有記錄的時候都對這個資料列重新整理：

        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• 在建立新記錄的時候把這個欄位設定為目前時間，但以後修改時，不再重新整理它：

        TIMESTAMP DEFAULT CURRENT_TIMESTAMP

• 在建立新記錄的時候把這個欄位設定為0，以後修改時重新整理它：

        TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• 在建立新記錄的時候把這個欄位設定為給定值，以後修改時重新整理它：

        TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss‘ ON UPDATE CURRENT_TIMESTAMP 

 

那比如原mysql的時區是-7:00，然後又新增一個讀庫，這個讀庫的時區反而是在+8:00，它的實際值會有不同嗎？我們來實驗下：

 

源庫：

 

$date -R

Tue, 26 Apr 2016 01:47:52 -0700

 

[email protected] 02:01:21>show variables like ‘%time_zone%‘;

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| system_time_zone | PDT    |

| time_zone        | SYSTEM |

+------------------+--------+

 

[email protected] 02:01:25>create table timestamp_test(t0 timestamp);

 

[email protected] 02:01:31>insert into timestamp_test values(current_timestamp);

 

[email protected] 02:01:46>select * from timestamp_test;

+---------------------+

| t0                  |

+---------------------+

| 2016-04-26 02:01:46 |

+---------------------+

 

新讀庫：

$date -R

Tue, 26 Apr 2016 16:52:39 +0800

 

[email protected] 05:02:54>show variables like ‘%time_zone%‘;

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| system_time_zone | CST    |

| time_zone        | SYSTEM |

+------------------+--------+

 

[email protected] 05:03:00>select * from timestamp_test;

+---------------------+

| t0                  |

+---------------------+

| 2016-04-26 17:01:46 |

+---------------------+

1 row in set (0.00 sec)

 

[email protected] 05:03:02>set time_zone=‘-7:00‘;

 

[email protected] 05:03:48>select * from timestamp_test;

+---------------------+

| t0                  |

+---------------------+

| 2016-04-26 02:01:46 |

+---------------------+

 

因為mysql的主備同步，同步的還是sql，就算是row模式的同步也只是把其實同步的值以物理塊的形式傳輸，實際還是要在目標端轉換為sql來執行，仍然是帶有時區資訊，會在用戶端進行時區轉換。

 

 

總結一下：

1、Oracle和MySQL中的timestamp的作用是不同的

• Oracle中，TIMESTAMP是對date的更高精度的一種儲存，是作為datetime的延展，但它不儲存時區資訊

• Oracle中，TIMESTAMP WITH TIME ZONE儲存時區資訊

• Oracle中，TIMESTAMP WITH LOCAL TIME ZONE不會儲存時區資訊，將時間資料轉換為資料庫時區的時間資料進行儲存，但不儲存時區資訊；用戶端檢索時，oracle會將資料庫中儲存的時間資料轉換為用戶端session時區的時間資料後返回給用戶端

• MYSQL中，的TIMESTAMP是為了更少的儲存單元（DATETIME為4位元組，TIMESTAMP為1個位元組）但是範圍為1970的某時的開始到2037年，而且會根據用戶端的時區判斷傳回值，MYSQL的TIMESTAMP時區敏感這點和ORACLE的TIMESTAMP WITH LOCAL TIME ZONE一致。

2、ORACLE和MYSQL的函數返回不一樣

• oracle讀取的時區資訊是以client端為準，CURRENT_TIMESTAMP都受到用戶端SESSION TIMEZONE影響，而SYSDATE,SYSTIMESTAP不受影響

• mysql讀取的時區資訊是以server端為準，NOW(),SYSDATE(),CURRENT_TIMESTAMP 均不受到用戶端串連時區影響

• DTS始終為client端，資料到達DTS時，都統一變成純字串

3、Oracle的DBTIMEZONE只和TIMESTAMP WITH LOCAL TIME ZONE有關。MySQL中的time_zone直接影響所有的timestamp取值。

4、為了返回一致的資料MYSQL設定TIME_ZONE參數即可，因為他是每個串連都會用到的，但是ORACLE最好使用SYSDATE或者SYSTIMESTAMP來直接取DB SERVER端時間。

5、MySQL修改時區資訊，只要CLIENT端的時區資訊不變，此無影響。

6、Oracle修改時間資訊，同理，TIMESTAMP WITH LOCAL TIME ZONE不受影響，TIMESTAMP和TIMESTAMP WITH TIME ZONE會發生變化。

7、如果在client中不指定時區資訊，oracle以client端的時區資訊為準，要進行轉換，mysql以server端的時區資訊為準。

 

oracle、mysql時區設定對timestamp的不同影響