MYSQL和ORACLE時區設定比較

MYSQL:

注意時區會影響TIMESTAMP的取值，預設為系統時區為TIME_ZONE=SYSTEM，

動态可以修改

set global  time_zone = '+8:00'; 

然後

my.cnf加上，永久修改

default-time_zone = '+8:00' 

The current time zone. This variable is used to initialize the time zone for each client that

connects. By default, the initial value of this is 'SYSTEM'(which means, “use the value of

system_time_zone”). 

也就是說每個連結都會使用這個參數作為他的預設時區，而TIMESTMAP是根據用戶端的時區不同

而不同，是以如果如果這個參數設定有誤會導緻TIMESTAMP時間出現問題

MYSQL的測試：

mysql> select now();

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

| now()               |

| 2015-06-12 12:10:13 |

1 row in set (0.00 sec)

mysql> select sysdate();

| sysdate()           |

| 2015-06-12 12:10:18 |

mysql> select current_timestamp from dual;

| current_timestamp   |

| 2015-06-12 12:10:46 |

mysql> set time_zone='+00:00';

Query OK, 0 rows affected (0.00 sec)

| 2015-06-12 04:11:01 |

| 2015-06-12 04:11:04 |

| 2015-06-12 04:11:06 |

1 row in set (0.01 sec)

可見MYSQL的NOW(),SYSDATE(),current_timestamp 均跟着用戶端時區走的。

oracle:

另外說一下ORACLE的時區問題，ORACLE時區分為

dbtimezone和sessiontimezone

其中DBTIMEZONE隻和TIMESTAMP WITH LOCAL TIME ZONE有關，在TIMESTAMP WITH LOCAL TIME ZONE類型存入資料庫中，實際上是轉換為DBTIMEZONE的時間，取出的時候

自動加上用戶端的SESSIONTIMEZONE的偏移量，文檔如下：

TimeStamp with Local Time Zone (TSLTZ) data stores internally the time converted to/from the database timezone (see point 3) from the timezone specified at insert/select time. 

Note that the data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data, the current DBTIMZONE is used. When users retrieve the data, Oracle Database returns it in the users' local session time zone from the current DBTIMEZONE.

而其他的時間類型和DBTIMEZONE無關，這也是為什麼有了TIMESTAMP WITCH LOCAL TIME ZONE修改DBTIMEZONE不行的原因，因為如果修改了DBTIMEZONE會導緻時間錯誤。

實際上MYSQL的TIMESTAMP類型和ORACLE的TIMESTAMP WITCH LOCAL TIME ZONE類型都是根據用戶端的時間來進行傳回時間，但是MYSQL可以簡單的設定

 time_zone參數來改變所有連接配接的時區，這樣傳回的時間能夠正确。

在說明一下ORACLE的TIMESTAMP和MYSQL的TIMESTAMP完全不同，

ORACLE的TIMESTAMP是為了精确到秒後6位，

而MYSQL的TIMESTAMP是為了更少的存儲單元（DATETIME為4位元組，TIMESTAMP為1個位元組）但是範圍為1970的某時的開始到2037年，而且會根據用戶端的時區判斷傳回值

而sessiontimezone，則影響着用戶端的時區，TIMESTAMP WITCH LOCAL TIME ZONE也會跟着這個時區進行改變，其他資料類型如DATE,TIMESTAMP等不會受到影響

可以再ALTER SESSION中設定也可以設定環境變量TZ=

如：

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

或者

export TZ='Asia/Shanghai';

做個簡單的實驗

SQL> desc testtim;

 Name                                      Null?    Type

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

 DATE1                                              TIMESTAMP(6)

 DATE2                                              TIMESTAMP(6) WITH TIME ZONE

 DATE3                                              TIMESTAMP(6) WITH LOCAL TIME ZONE

SQL> select * from testtim;

DATE1

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

DATE2

DATE3

12-JUN-15 11.40.02.000000 AM

12-JUN-15 11.40.02.000000 AM +08:00

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

Session altered.

11-JUN-15 10.40.02.000000 PM

最後ORACLE中常用的取時間函數的不同：

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP, that is date time similar to CURRENT_DATE but the datatype is TIMESTAMP.

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE, that is date time similar to CURRENT_DATE but the datatype is TIMESTAMP WITH TIME ZONE.

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE. Unlike SYSDATE, which you can set to a constant using FIXED_DATE, SYSTIMESTAMP will give the system date even though FIXED_DATE is set.

"SYSDATE" and "SYSTIMESTAMP" are purely dependent on the operating system clock, hence it IS depending on the timezone information of this operating system and/or the operating system settings when the database and listener where started.

很顯然LOCALTIMESTAMP和CURRENT_TIMESTAMP都受到用戶端SESSIONTIMEZONE影響，而SYSDATE的不受影響他傳回的一定是伺服器ORACLE 設定的SESSIONTIMEZONE的時間。

如果需要更改用戶端的SYSDATE的取值必須

1、修改伺服器下ORACLE使用者的TZ

2、重新開機資料庫

export  TZ='UTC';

後檢視服務端SYSDATE

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

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

2015-06-12 04:06:34

按理說用戶端也應該傳回這個值

但是用戶端任然傳回

TO_CHAR(SYSDATE,'YYYY-MM-DDHH2

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

2015-06-12 12:08:19

重新開機後

2015-06-12 04:09:19

用戶端正常。

總結一下：

1、ORACLE和MYSQL的timestamp不同

MYSQL的TIMESTAMP時區敏感這點和ORACLE的TIMESTAMP WITH LOCAL TIME ZONE一緻。

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

ORACLE：

LOCALTIMESTAMP和CURRENT_TIMESTAMP都受到用戶端SESSIONTIMEZONE影響，而SYSDATE,SYSTIMESTAP的不受影響他傳回的一定是伺服器ORACLE 設定的SESSIONTIMEZONE的時間

NOW(),SYSDATE(),CURRENT_TIMESTAMP 均受到用戶端連接配接時區影響。

3、oracle的DBTIMEZONE用處不大，隻和TIMESTAMP WITH LOCAL TIME ZONE有關。

4、為了傳回一緻的資料MYSQL設定TIME_ZONE參數即可因為他是每個連接配接都會用到的，但是ORACLE最好使用SYSDATE或者SYSTIMESTAMP來直接取服務端的SESSIONTIMEZONE下的時間。