[每日一题] 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是，时间增加三个小时
