TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型

TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型

TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes 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 datatype is useful for collecting and evaluating date information across geographic regions.
TIMESTAMP WITH TIME ZONE存储时区信息。

TIMESTAMP WITH LOCAL TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value. 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 offset 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. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.
TIMESTAMP WITH LOCAL TIME ZONE  将时间数据以数据库时区进行规范化后进行存储（即将时间数据转换为数据库时区的时间数据进行存储），但不存储时区信息;客户端检索时，oracle会将数据库中存储的时间数据转换为客户端session时区的时间数据后返回给客户端。


下面的例子能很好的区别TIMESTAMP WITH TIME ZONE与TIMESTAMP WITH LOCAL TIME ZONE数据类型：

SQL> select dbtimezone from dual;   --数据库时区

DBTIME
------
+00:00

SQL> select sessiontimezone from dual;  --session会话时区

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

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

表已创建。

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-2月 -12 10.22.45.697000 上午 +08:00

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-2月 -12 10.24.13.446000 上午 +08:00

SQL> select sysdate from dual;

SYSDATE
-------------------
2012-02-06 10:24:25

SQL> insert into timezone_test values(1,TO_TIMESTAMP('2012-2-6 14:10:10.123000',
  2   'YYYY-MM-DD HH24:MI:SS.FF'),TO_TIMESTAMP('2012-2-6 14:10:10.123000', 'YYYY-MM-DD HH24:MI:SS.FF'));

已创建 1 行。

SQL>  insert into timezone_test values(2,TO_TIMESTAMP('2012-2-6 5:10:10.123000',
  2   'YYYY-MM-DD HH24:MI:SS.FF'),TO_TIMESTAMP('2012-2-6 5:10:10.123000', 'YYYY-MM-DD HH24:MI:SS.FF'));

已创建 1 行。

SQL>  insert into timezone_test values(3,TO_DATE('2012-2-6 14:10:10',
  2   'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2012-2-6 14:10:10', 'YYYY-MM-DD HH24:MI:SS'));

已创建 1 行。

SQL> commit;

提交完成。

SQL> set linesize 120
SQL> col t1 for a50
SQL> col t2 for a50
SQL> select * from timezone_test;

        ID T1                                                 T2
---------- -------------------------------------------------- --------------------------------------------------
         1 06-2月 -12 02.10.10.123000 下午 +08:00             06-2月 -12 02.10.10.123000 下午
         2 06-2月 -12 05.10.10.123000 上午 +08:00             06-2月 -12 05.10.10.123000 上午
         3 06-2月 -12 02.10.10.000000 下午 +08:00             06-2月 -12 02.10.10.000000 下午

SQL> select sessiontimezone from dual;

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

--设置不同的时区，观察两种数据类型的区别。
SQL> alter session set time_zone='+06:00';  

会话已更改。

SQL> select * from timezone_test;

        ID T1                                                 T2
---------- -------------------------------------------------- --------------------------------------------------
         1 06-2月 -12 02.10.10.123000 下午 +08:00             06-2月 -12 12.10.10.123000 下午
         2 06-2月 -12 05.10.10.123000 上午 +08:00             06-2月 -12 03.10.10.123000 上午
         3 06-2月 -12 02.10.10.000000 下午 +08:00             06-2月 -12 12.10.10.000000 下午

SQL> alter session set time_zone='+08:00';

会话已更改。

SQL> select * from timezone_test;

        ID T1                                                 T2
---------- -------------------------------------------------- --------------------------------------------------
         1 06-2月 -12 02.10.10.123000 下午 +08:00             06-2月 -12 02.10.10.123000 下午
         2 06-2月 -12 05.10.10.123000 上午 +08:00             06-2月 -12 05.10.10.123000 上午
         3 06-2月 -12 02.10.10.000000 下午 +08:00             06-2月 -12 02.10.10.000000 下午

SQL> alter session set time_zone='+09:00';

会话已更改。

SQL> select * from timezone_test;

        ID T1                                                 T2
---------- -------------------------------------------------- --------------------------------------------------
         1 06-2月 -12 02.10.10.123000 下午 +08:00             06-2月 -12 03.10.10.123000 下午
         2 06-2月 -12 05.10.10.123000 上午 +08:00             06-2月 -12 06.10.10.123000 上午
         3 06-2月 -12 02.10.10.000000 下午 +08:00             06-2月 -12 03.10.10.000000 下午

SQL> alter session set time_zone='+07:00';

会话已更改。

SQL> select * from timezone_test;

        ID T1                                                 T2
---------- -------------------------------------------------- --------------------------------------------------
         1 06-2月 -12 02.10.10.123000 下午 +08:00             06-2月 -12 01.10.10.123000 下午
         2 06-2月 -12 05.10.10.123000 上午 +08:00             06-2月 -12 04.10.10.123000 上午
         3 06-2月 -12 02.10.10.000000 下午 +08:00             06-2月 -12 01.10.10.000000 下午

SQL>

参考：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i53219