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下的时间。