Interval的知识点

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#SQLRF00221

 

IntervalLiterals

 

An interval literal specifies a period of time. You can specifythese differences in terms of years and months, or in terms ofdays, hours, minutes, and seconds. Oracle Database supports twotypes of interval literals,YEAR TOMONTH and DAY TOSECOND. Each type contains a leading field and maycontain a trailing field. The leading field defines the basic unitof date or time being measured. The trailing field defines thesmallest increment of the basic unit being considered. For example,aYEAR TO MONTH intervalconsiders an interval of years to the nearest month. ADAYTO MINUTE intervalconsiders an interval of days to the nearest minute.

If you have date data in numeric form, then you can use theNUMTOYMINTERVAL orNUMTODSINTERVALconversion function to convert the numeric data into intervalvalues.

Interval literals are used primarily with analyticfunctions.

See Also:

"AnalyticFunctions", NUMTODSINTERVAL, NUMTOYMINTERVAL, and Oracle Data Warehousing Guide

 

INTERVAL YEAR TO MONTH

 

Specify YEAR TO MONTHinterval literals using the following syntax:

interval_year_to_month::=

Description of the illustrationinterval_year_to_month.gif

where

• 'integer[-integer]' specifies integer values for theleading and optional trailing field of the literal. If the leadingfield isYEAR and the trailing field isMONTH, then the range of integer values for the monthfield is 0 to 11.

   --YEAR 是主域，MONTH 是尾域。月份的域的整数值范围为0到11.

• precision is the maximum numberof digits in the leading field. The valid range of the leadingfield precision is 0 to 9 and its default value is2.--精度是指在主域最大有效位，主域精度有效范围(--既YEAR的精度)为0到9，默认值为2。例如：

    1、SELECTINTERVAL '300' MONTH,INTERVAL '128' Month from dual.它的返回结果是：+25-00  +10-08。  此次SQL语句，两个类型的数据，采用默认精度为2.所以300个月份转换刚好25年又0个月，而128个月转换为年刚好是10年又8个月。

   2、然而主域采用默认的精度为2。如SQL:SELECT INTERVAL '300' YEAR from dual.会得到报错信息。系统在转换为精度为2的数据转不了。（见下）

 

 

Restriction on the LeadingField If youspecify a trailing field, it must be less significant than theleading field. For example,INTERVAL'0-1' MONTH TOYEAR is notvalid.--如果指定一个尾域，尾域必须要显著的小于主域的值。INTERVAL'0-1'MONTH TOYEAR是无效的。

 

The following INTERVAL YEARTO MONTH literal indicates an interval of123 years, 2 months:

INTERVAL '123-2' YEAR(3) TO MONTH

Examples of the other forms of the literal follow, includingsome abbreviated versions:

Form of Interval Literal	Interpretation
INTERVAL '123-2' YEAR(3)TO MONTH	An interval of 123years, 2 months. You must specify the leading field precision if itis greater than the default of 2 digits.
INTERVAL '123'YEAR(3)	An interval of 123years 0 months.
INTERVAL '300' MONTH(3)	An interval of 300 months.
INTERVAL '4'YEAR	Maps to INTERVAL'4-0' YEAR TO MONTH and indicates 4 years.
INTERVAL '50'MONTH	Maps to INTERVAL'4-2' YEAR TO MONTH and indicates 50 months or 4 years 2months.
INTERVAL '123' YEAR	Returns an error, because the default precision is 2, and'123' has 3 digits.

You can add or subtract one INTERVALYEAR TOMONTH literal to orfrom another to yield another INTERVALYEARTO MONTH literal. Forexample:

INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = 
INTERVAL '6-11' YEAR TO MONTH

 

INTERVAL DAY TO SECOND

 

Specify DAY TO SECONDinterval literals using the following syntax:

interval_day_to_second::=

Description of the illustrationinterval_day_to_second.gif

where

• integer specifies the number ofdays. If this value contains more digits than the number specifiedby the leading precision, then Oracle returns anerror.

  --整数指定的是天数。如果这个值包含的有效数位比在主域中指定的有效数位要大，系统返回一个错误。这和数据类型：INTERVAL YEARTO MONTH一样的。

• time_expr specifies a time inthe formatHH[:MI[:SS[.n]]] orMI[:SS[.n]] or SS[.n], where n specifies the fractional partof a second. Ifn contains more digits than thenumber specified byfractional_seconds_precision,thenn isrounded to the number of digits specified by thefractional_seconds_precisionvalue. You can specifytime_expr following an integerand a space only if the leading field isDAY.

time_expr指定了时间的格式为：HH[:MI[:SS[.n]]]或者MI[:SS[.n]] 或者SS[.n], 这n指定了秒的小数部分。如果n包括了有效数位超过在fractional_seconds_precision子句中指定的数位，那么n会被四舍五入到fractional_seconds_precision子句中指定的数位。

• leading_precision is the numberof digits in the leading field. Accepted values are 0 to 9. Thedefault is 2.

 

• fractional_seconds_precisionis the number of digits in the fractional part of theSECOND datetime field.Accepted values are 1 to 9. The default is 6.

   --fractional_seconds_precision这子句指定了在秒的时间域的小数部分具体有效数位。这小数位的默认精度是6，如果实际超出规定的精度，会进行四舍五入得到具体值。

 

Restrictionon the Leading Field: If youspecify a trailing field, it must be less significant than theleading field. For example,INTERVALMINUTE TO DAY is not valid.As a result of this restriction, ifSECOND is theleading field, the interval literal cannot have any trailingfield.

 

The valid range of values for the trailing field are asfollows:

• HOUR: 0 to 23

• MINUTE: 0 to 59

• SECOND: 0 to 59.999999999

Examples of the various forms of INTERVALDAY TOSECOND literalsfollow, including some abbreviated versions:

Form of Interval Literal	Interpretation
INTERVAL '4 5:12:10.222'DAY TO SECOND(3)	4 days, 5 hours, 12minutes, 10 seconds, and 222 thousandths of a second.
INTERVAL '4 5:12' DAY TOMINUTE	4 days, 5 hours and 12minutes.
INTERVAL '400 5' DAY(3)TO HOUR	400 days 5 hours.
INTERVAL '400'DAY(3)	400 days.
INTERVAL'11:12:10.2222222' HOUR TO SECOND(7)	11 hours, 12 minutes,and 10.2222222 seconds.
INTERVAL '11:20' HOUR TOMINUTE	11 hours and 20minutes.
INTERVAL '10'HOUR	10 hours.
INTERVAL '10:22' MINUTETO SECOND	10 minutes 22seconds.
INTERVAL '10'MINUTE	10 minutes.
INTERVAL '4'DAY	4 days.
INTERVAL '25'HOUR	25 hours.
INTERVAL '40'MINUTE	40 minutes.
INTERVAL '120'HOUR(3)	120 hours.
INTERVAL '30.12345'SECOND(2,4)	30.1235 seconds. Thefractional second '12345' is rounded to '1235' because theprecision is 4.

You can add or subtract one DAY TOSECOND interval literal from anotherDAYTO SECOND literal. For example.

INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND