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 |
+---------------------+
1 row in set (0.00 sec)


mysql> select current_timestamp from dual;
+---------------------+
| current_timestamp   |
+---------------------+
| 2015-06-12 12:10:46 |
+---------------------+
1 row in set (0.00 sec)


mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)


mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2015-06-12 04:11:01 |
+---------------------+
1 row in set (0.00 sec)


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-12 04:11:04 |
+---------------------+
1 row in set (0.00 sec)


mysql> select current_timestamp from dual;
+---------------------+
| current_timestamp   |
+---------------------+
| 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
12-JUN-15 11.40.02.000000 AM


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

Session altered.

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
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
按理说客户端也应该返回这个值
但是客户端任然返回

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2015-06-12 12:08:19

重启后
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2015-06-12 04:09:19

客户端正常。

总结一下：
1、ORACLE和MYSQL的timestamp不同
ORACLE的TIMESTAMP是为了精确到秒后6位，
而MYSQL的TIMESTAMP是为了更少的存储单元（DATETIME为4字节，TIMESTAMP为1个字节）但是范围为 1970的某时的开始到2037年，而且会根据客户端的时区判断返回值
MYSQL的TIMESTAMP时区敏感这点和ORACLE的TIMESTAMP WITH LOCAL TIME ZONE一致。
2、ORACLE和MYSQL的函数返回不一样
ORACLE：
LOCALTIMESTAMP和 CURRENT_TIMESTAMP都受到客户端SESSIONTIMEZONE影响 ，而 SYSDATE,SYSTIMESTAP的不受影响他返回的一定是服务器ORACLE 设置的SESSIONTIMEZONE的时间
MYSQL:
NOW(),SYSDATE(), CURRENT_TIMESTAMP 均受到客户端连接时区影响。

3、oracle的DBTIMEZONE用处不大，只和 TIMESTAMP WITH LOCAL TIME ZONE有关。
4、为了返回一致的数据MYSQL设置TIME_ZONE参数即可因为他是每个连接都会用到的，但是ORACLE最好使用SYSDATE或者SYSTIMESTAMP来直接取服务端的SESSIONTIMEZONE下的时间。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1802858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7728585/viewspace-1802858/