当前位置:操作系统 > Unix/Linux >>

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

[每日一题] OCP1z0-047 :2013-08-10 INTERVAL DAY TO SECOND
 
 
正解答案是:B
 
“根据题意要求,需要存储的是一个时间间隔的数据,且方便加减,所以首先排除timestamp类型。然后题目要求最大的周期是30天,所以year to month排除,因为该项只能精确到月。然后就剩下B“
 
官方文档解释:
INTERVAL YEAR TO MONTH Datatype
INTERVAL YEAR TO MONTH stores a period of time using theYEAR andMONTH datetime fields. This datatype is useful for representing the differencebetween two datetime values when only the year and month values aresignificant.
Specify INTERVAL YEAR TO MONTH as follows:
INTERVAL YEAR [(year_precision)] TO MONTH
Datetime and Interval Examples
The following example shows how to declare some datetimeand interval datatypes.
 
[html] 
hr@MYDB> hr@MYDB> SELECT last_name, sysdate,hire_date,EXTRACT(YEAR FROM (SYSDATE - hire_date) YEAR TO MONTH )  
  2     || ' years '  
  3     || EXTRACT(MONTH FROM (SYSDATE - hire_date) YEAR TO MONTH )  
  4     || ' months'  "Interval"  
  5  FROM employees ;  
  
LAST_NAME                 SYSDATE      HIRE_DATE    Interval  
------------------------- ------------ ------------ --------------------------------------------------  
Dilly                     10-AUG-13    13-AUG-05    8 years 0 months  
Gates                     10-AUG-13    11-JUL-06    7 years 1 months  
Perkins                   10-AUG-13    19-DEC-07    5 years 8 months  
Bell                      10-AUG-13    04-FEB-04    9 years 6 months中间结果省略  
Bull                      10-AUG-13    20-FEB-05    8 years 6 months  
Dellinger                 10-AUG-13    24-JUN-06    7 years 2 months  
Cabrio                    10-AUG-13    07-FEB-07    6 years 6 months  
Chung                     10-AUG-13    14-JUN-05    8 years 2 months  
  
107 rows selected.  

INTERVAL DAY TO SECOND Datatype
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds.This datatype is useful for representing the precise difference between twodatetime values.
Specify this datatype as follows:
INTERVAL DAY [(day_precision)] 
   TO SECOND [(fractional_seconds_precision)]


TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH TIME ZONE is a variant ofTIMESTAMP that includes a time zoneoffset in its value. The time zone offset is the difference (in hoursand minutes) between local time and UTC (Coordinated Universal Time—formerlyGreenwich Mean Time). This datatype is useful for collecting and evaluatingdate information across geographic regions.
Specify the TIMESTAMP WITH TIME ZONE datatype as follows:
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

 

 
[html] 
oe@MYDB> col ORDER_DATE for a30  
oe@MYDB> col Interval for a30  
oe@MYDB> SELECT order_id,sysdate,order_date,  
  2     EXTRACT(DAY FROM (SYSDATE - order_date) DAY TO SECOND )  
  3     || ' days '  
  4     || EXTRACT(HOUR FROM (SYSDATE - order_date) DAY TO SECOND )  
  5     || ' hours' "Interval"  
  6  FROM orders;  
  
  ORDER_ID SYSDATE      ORDER_DATE                     Interval  
---------- ------------ ------------------------------ ------------------------------  
2442 10-AUG-13    28-JUL-90 02.22.59.662632 AM   8414 days 21 hours  
2443 10-AUG-13    28-JUL-90 03.34.16.562632 AM   8414 days 20 hours  
2445 10-AUG-13    28-JUL-90 05.34.38.362632 AM   8414 days 18 hours  
2418 10-AUG-13    21-MAR-96 08.18.21.862632 AM   6351 days 15 hours 2415 10-AUG-13    30-MAR-97 05.34.50.545196 AM   5977 days 18 hours  
2357 10-AUG-13    09-JAN-98 12.19.44.123456 PM   5692 days 11 hours  

 

省略中间结果  
2447 10-AUG-13    27-JUL-00 10.59.10.223344 PM   4762 days 0 hours  
2441 10-AUG-13    02-AUG-00 01.22.48.734526 AM   4756 days 22 hours  
13 10-AUG-13    04-AUG-13 09.32.52.000000 PM   6 days 2 hours  
130 10-AUG-13    04-AUG-13 10.55.36.000000 PM   6 days 0 hours  
  
107 rows selected.  
 
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是,时间增加三个小时
 
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,