[每日一题] OCP1z0-047 :2013-08-10 INTERVAL DAY TO SECOND(二)

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 fromTIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database timezone, and the time zone offset is not stored as part of the column data. When auser retrieves the data, Oracle returns it in the user's local session timezone. The time zone offset is the difference (in hours and minutes) betweenlocal time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time).This datatype is useful for displaying date information in the time zone of theclient system in a two-tier application.

Specify the TIMESTAMP WITH LOCAL TIME ZONE datatype as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

timestamp with local time zone 和timesatamp with time zone的最大区别就是，前者在用户提交时间给 数据库的时，该类型会转换成数据库的时区来保存数据，即数据库保存的时间是数据库本地时区，当别的用户访问数据库时oracle会自动将该时间转换成当前客户端的时间。

实验：

1、创建表
[html] 
gyj@MYDB> CREATE  TABLE  TIME_TEST(  
  2  TIME          DATE,  
  3  TIMESTP      TIMESTAMP(3),  
  4  TIMESTP_TZ  TIMESTAMP(3) WITH TIME ZONE,  
  5  TIMESTP_LTZ  TIMESTAMP(3) WITH LOCAL TIME ZONE);  
  
Table created.  

2、插入数据
[html] 
gyj@MYDB>





 INSERT INTO TIME_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);  
  
1 row created.  
  
gyj@MYDB> commit;  
  
Commit complete.  

3、 查询dbtimezone和sessiontimezone的值
[html] 
gyj@MYDB> select dbtimezone ,sessiontimezone from dual;  
DBTIME SESSIONTIMEZONE  
------ -----------------------------------------------------------  
+08:00 +08:00  

4、 查看数据的值
[html] 
gyj@MYDB> col TIMESTP for a30  
gyj@MYDB> col TIMESTP_TZ for a35  
gyj@MYDB> col TIMESTP_LTZ for a30  
gyj@MYDB>  SELECT * FROM TIME_TEST;  
  
TIME         TIMESTP                        TIMESTP_TZ                          TIMESTP_LTZ  
------------ ------------------------------ ----------------------------------- ------------------------------  
10-AUG-13    10-AUG-13 11.09.16.000 PM      10-AUG-13 11.09.16.000 PM +08:00    10-AUG-13 11.09.16.000 PM  

5、 修改会话的time_zone值
[html] 
gyj@MYDB> alter session set time_zone='+11:00';  
  
Session altered.  

6、 查看结果
[html] 
gyj@MYDB>  SELECT * FROM TIME_TEST;  
  
TIME         TIMESTP                        TIMESTP_TZ                          TIMESTP_LTZ  
------------ ------------------------------ ----------------------------------- ------------------------------  
10-AUG-13    10-AUG-13 11.09.16.000 PM      10-AUG-13 11.09.16.000 PM +08:00    11-AUG-13 02.09.16.000 AM  

从上面的实验可以看出二者的去区别，当session的时区由8变为11是，时间增加三个小时