Interval的知识点
来源:互联网 发布:网络报警怎么报 编辑:程序博客网 时间:2024/05/18 01:12
文档地址: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
TO
MONTH
and DAY
TO
SECOND
. 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. ADAY
TO
MINUTE
intervalconsiders an interval of days to the nearest minute.
If you have date data in numeric form, then you can use theNUMTOYMINTERVAL
orNUMTODSINTERVAL
conversion function to convert the numeric data into intervalvalues.
Interval literals are used primarily with analyticfunctions.
See Also:
"AnalyticFunctions",NUMTODSINTERVAL,NUMTOYMINTERVAL, and Oracle Data Warehousing GuideINTERVAL YEAR TO MONTH
Specify YEAR
TO
MONTH
interval 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.
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。例如:
Restriction on the LeadingField INTERVAL
'0-1
'MONTH
TO
YEAR
is notvalid.--如果指定一个尾域,尾域必须要显著的小于主域的值。INTERVAL'0-1
'MONTH
TO
YEAR是无效的。
The following INTERVAL
YEAR
TO
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:
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 INTERVAL
YEAR
TO
MONTH
literal to orfrom another to yield anotherINTERVAL
YEAR
TO
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
SECOND
interval 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.
time_expr
specifies a time inthe formatHH[:MI[:SS[.n]]]
orMI[:SS[.n]]
orSS[.n]
, wheren
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_precision
value. 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_precision
is the number of digits in the fractional part of theSECOND
datetime field.Accepted values are 1 to 9. The default is 6.
Restrictionon the Leading Field: INTERVAL
MINUTE
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 23MINUTE
: 0 to 59SECOND
: 0 to 59.999999999
Examples of the various forms of INTERVAL
DAY
TO
SECOND
literalsfollow, including some abbreviated versions:
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
TO
SECOND
interval literal from anotherDAY
TO
SECOND
literal. For example.
INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND
- Interval的知识点
- INTERVAL的使用
- interval
- INTERVAL
- interval
- interval使用的一些例子
- 校门外的区间(interval)
- SQL SERVER的recovery interval
- 无线路由的beacon interval
- add_months 和interval 的区别
- QUESTION 19 INTERVAL的用法
- UsageStatsManager.queryUsageStats的参数interval
- Observable.interval()不起作用的解决办法
- Oracle INTERVAL(INTERVAL DAY TO SECOND,INTERVAL YEAR TO MONTH)数据类型的使用
- oracle interval日期函数的bug!
- timer的interval值最大到底是多少
- NSMutableURLRequest timeout interval 不起作用的原因
- Oracle11g的新增特性—INTERVAL分区
- update的一些注意的语法
- alter table语法
- TO_CHAR函数详解
- 甲骨文语句级别的回滚
- oracle连接叙述
- Interval的知识点
- ORDER BY的一些小知识点
- SYSDATE函数
- 搜索Maven仓库
- NVL2函数
- CSharp - Comparison between IComparer and IComparable
- ORA-03137: TTC protocol internal error : [12333]错误一例
- Oracle 闪回特性(Flashback Version、Flashback Transaction)
- GRANT使用