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

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/9887591

正解答案是：B

   引用akagea同学的解释

http://www.itpub.net/thread-1808740-1-1.html

“根据题意要求，需要存储的是一个时间间隔的数据，且方便加减，所以首先排除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.

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 monthsGates                     10-AUG-13    11-JUL-06    7 years 1 monthsPerkins                   10-AUG-13    19-DEC-07    5 years 8 monthsBell                      10-AUG-13    04-FEB-04    9 years 6 months中间结果省略Bull                      10-AUG-13    20-FEB-05    8 years 6 monthsDellinger                 10-AUG-13    24-JUN-06    7 years 2 monthsCabrio                    10-AUG-13    07-FEB-07    6 years 6 monthsChung                     10-AUG-13    14-JUN-05    8 years 2 months107 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

oe@MYDB> col ORDER_DATE for a30oe@MYDB> col Interval for a30oe@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 hours2443 10-AUG-13    28-JUL-90 03.34.16.562632 AM   8414 days 20 hours2445 10-AUG-13    28-JUL-90 05.34.38.362632 AM   8414 days 18 hours2418 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 hours2357 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 hours2441 10-AUG-13    02-AUG-00 01.22.48.734526 AM   4756 days 22 hours13 10-AUG-13    04-AUG-13 09.32.52.000000 PM   6 days 2 hours130 10-AUG-13    04-AUG-13 10.55.36.000000 PM   6 days 0 hours107 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 fromTIMESTAMPWITHTIMEZONE 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、创建表

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、插入数据

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

3、 查询dbtimezone和sessiontimezone的值

gyj@MYDB> select dbtimezone ,sessiontimezone from dual;DBTIME SESSIONTIMEZONE------ -----------------------------------------------------------+08:00 +08:00

4、 查看数据的值

gyj@MYDB> col TIMESTP for a30gyj@MYDB> col TIMESTP_TZ for a35gyj@MYDB> col TIMESTP_LTZ for a30gyj@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值

gyj@MYDB> alter session set time_zone='+11:00';Session altered.

6、 查看结果

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是，时间增加三个小时

 

别外可以参考这篇文章：

http://space.itpub.net/9765498/viewspace-539881 

QQ：252803295

学习交流QQ群：
DSI&Core Search  Ⅰ 群：127149411（技术:已满）
DSI&Core Search  Ⅱ 群：177089463（技术:未满）
DSI&Core Search  Ⅲ 群：284596437（技术:未满）
DSI&Core Search  Ⅳ 群：192136702（技术:未满）
DSI&Core Search  Ⅴ 群：285030382（闲聊:未满）

MAIL：[e-mail]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：http://weibo.com/guoyJoe0218

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：   http://education.oracle.com/education/otn/YGuo.HTM