INTERVAL YEAR TO MONTH和TIMESTAMP WITH TIME ZONE 等

转载请注明出处：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 TOMONTH 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 are significant.

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] view plaincopy

1. hr@MYDB> hr@MYDB> SELECT last_name, sysdate,hire_date,EXTRACT(YEAR FROM (SYSDATE - hire_date) YEAR TO MONTH )  
2.   2     || ' years '  
3.   3     || EXTRACT(MONTH FROM (SYSDATE - hire_date) YEAR TO MONTH )  
4.   4     || ' months'  "Interval"  
5.   5  FROM employees ;  
6.   
7. LAST_NAME                 SYSDATE      HIRE_DATE    Interval  
8. ------------------------- ------------ ------------ --------------------------------------------------  
9. Dilly                     10-AUG-13    13-AUG-05    8 years 0 months  
10. Gates                     10-AUG-13    11-JUL-06    7 years 1 months  
11. Perkins                   10-AUG-13    19-DEC-07    5 years 8 months  
12. Bell                      10-AUG-13    04-FEB-04    9 years 6 months中间结果省略  
13. Bull                      10-AUG-13    20-FEB-05    8 years 6 months  
14. Dellinger                 10-AUG-13    24-JUN-06    7 years 2 months  
15. Cabrio                    10-AUG-13    07-FEB-07    6 years 6 months  
16. Chung                     10-AUG-13    14-JUN-05    8 years 2 months  
17.   
18. 107 rows selected.  

注释：

例如，

  SYSDATE =10-AUG-13 ，  HIRE_DATE=11-JUL-06 ，则  SYSDATE =AUG-13，  HIRE_DATE=JUL-06，则AUG-JUL= 1 months ，13-06= 7 years，即 Interval =7 years 1 months  ；

SYSDATE =10-AUG-13 ，  HIRE_DATE=19-DEC-07 ，则  SYSDATE =AUG-13，  HIRE_DATE=DEC-07，则AUG-DEC= 8months -1 years，13-07= 6 years，即 Interval  =8months  6 years -1 years=5 years 8 months  ；

INTERVAL DAY TO SECOND Datatype

INTERVAL DAY TOSECOND 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)]

 

[html] view plaincopy

1. oe@MYDB> col ORDER_DATE for a30  
2. oe@MYDB> col Interval for a30  
3. oe@MYDB> SELECT order_id,sysdate,order_date,  
4.   2     EXTRACT(DAY FROM (SYSDATE - order_date) DAY TO SECOND )  
5.   3     || ' days '  
6.   4     || EXTRACT(HOUR FROM (SYSDATE - order_date) DAY TO SECOND )  
7.   5     || ' hours' "Interval"  
8.   6  FROM orders;  
9.   
10.   ORDER_ID SYSDATE      ORDER_DATE                     Interval  
11. ---------- ------------ ------------------------------ ------------------------------  
12. 2442 10-AUG-13    28-JUL-90 02.22.59.662632 AM   8414 days 21 hours  
13. 2443 10-AUG-13    28-JUL-90 03.34.16.562632 AM   8414 days 20 hours  
14. 2445 10-AUG-13    28-JUL-90 05.34.38.362632 AM   8414 days 18 hours  
15. 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  
16. 2357 10-AUG-13    09-JAN-98 12.19.44.123456 PM   5692 days 11 hours  
17. 省略中间结果  
18. 2447 10-AUG-13    27-JUL-00 10.59.10.223344 PM   4762 days 0 hours  
19. 2441 10-AUG-13    02-AUG-00 01.22.48.734526 AM   4756 days 22 hours  
20. 13 10-AUG-13    04-AUG-13 09.32.52.000000 PM   6 days 2 hours  
21. 130 10-AUG-13    04-AUG-13 10.55.36.000000 PM   6 days 0 hours  
22.   
23. 107 rows selected.  

TIMESTAMP WITH TIME ZONE Datatype

TIMESTAMP WITH TIMEZONE 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 WITHTIME ZONE datatype as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE Datatype

TIMESTAMP WITH LOCALTIME 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 WITHLOCAL 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] view plaincopy

1. gyj@MYDB> CREATE  TABLE  TIME_TEST(  
2.   2  TIME          DATE,  
3.   3  TIMESTP      TIMESTAMP(3),  
4.   4  TIMESTP_TZ  TIMESTAMP(3) WITH TIME ZONE,  
5.   5  TIMESTP_LTZ  TIMESTAMP(3) WITH LOCAL TIME ZONE);  
6.   
7. Table created.  

2、插入数据

[html] view plaincopy

1. gyj@MYDB> INSERT INTO TIME_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);  
2.   
3. 1 row created.  
4.   
5. gyj@MYDB> commit;  
6.   
7. Commit complete.  

3、查询dbtimezone和sessiontimezone的值

[html]view plaincopy

1. gyj@MYDB> select dbtimezone ,sessiontimezone from dual;  
2. DBTIME SESSIONTIMEZONE  
3. ------ -----------------------------------------------------------  
4. +08:00 +08:00  

4、 查看数据的值

[html] view plaincopy

1. gyj@MYDB> col TIMESTP for a30  
2. gyj@MYDB> col TIMESTP_TZ for a35  
3. gyj@MYDB> col TIMESTP_LTZ for a30  
4. gyj@MYDB>  SELECT * FROM TIME_TEST;  
5.   
6. TIME         TIMESTP                        TIMESTP_TZ                          TIMESTP_LTZ  
7. ------------ ------------------------------ ----------------------------------- ------------------------------  
8. 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] view plaincopy

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

6、 查看结果

[html] view plaincopy

1. gyj@MYDB>  SELECT * FROM TIME_TEST;  
2.   
3. TIME         TIMESTP                        TIMESTP_TZ                          TIMESTP_LTZ  
4. ------------ ------------------------------ ----------------------------------- ------------------------------  
5. 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

参考：

Oracle extract函数用法