oracle中的INTERVAL函数详解.docx
《oracle中的INTERVAL函数详解.docx》由会员分享，可在线阅读，更多相关《oracle中的INTERVAL函数详解.docx（9页珍藏版）》请在冰豆网上搜索。
oracle中的INTERVAL函数详解
oracle中的INTERVAL函数详解
oracle中的INTERVAL函数详解
INTERVALYEARTOMONTH数据类型
Oracle语法:
INTERVAL'integer[-integer]'{YEAR|MONTH}[（precision）][TO{YEAR|MONTH}]
该数据类型常用来表示一段时间差,注意时间差只精确到年和月.precision为年或月的精确域,有效范围是0到9,默认值为2.
eg:
INTERVAL'123-2'YEAR（3）TOMONTH
表示:
123年2个月,"YEAR（3）"表示年的精度为3,可见"123"刚好为3为有效数值,如果该处YEAR（n）,n<3就会出错,注意默认是2.
INTERVAL'123'YEAR（3）
表示:
123年0个月
INTERVAL'300'MONTH（3）
表示:
300个月,注意该处MONTH的精度是3啊.
INTERVAL'4'YEAR
表示:
4年,同INTERVAL'4-0'YEARTOMONTH是一样的
INTERVAL'50'MONTH
表示:
50个月,同INTERVAL'4-2'YEARTOMONTH是一样
INTERVAL'123'YEAR
表示:
该处表示有错误,123精度是3了,但系统默认是2,所以该处应该写成INTERVAL'123'YEAR（3）或"3"改成大于3小于等于9的数值都可以的
INTERVAL'5-3'YEARTOMONTH+INTERVAL'20'MONTH=
INTERVAL'6-11'YEARTOMONTH
表示:
5年3个月+20个月=6年11个月
与该类型相关的函数:
NUMTODSINTERVAL（n,'interval_unit'）
将n转换成interval_unit所指定的值,interval_unit可以为:
DAY,HOUR,MINUTE,SECOND
注意该函数不可以转换成YEAR和MONTH的.
NUMTOYMINTERVAL（n,'interval_unit'）
interval_unit可以为:
YEAR,MONTH
eg:
（OracleVersion9204,RedHatLinux9.0）
SQL>selectnumtodsinterval（100,'DAY'）fromdual;
NUMTODSINTERVAL（100,'DAY'）
---------------------------------------------------------------------------
+00000010000:
00:
00.000000000
SQL>c/DAY/SECOND
1*selectnumtodsinterval（100,'SECOND'）fromdual
SQL>/
NUMTODSINTERVAL（100,'SECOND'）
---------------------------------------------------------------------------
+00000000000:
01:
40.000000000
SQL>c/SECOND/MINUTE
1*selectnumtodsinterval（100,'MINUTE'）fromdual
SQL>/
NUMTODSINTERVAL（100,'MINUTE'）
---------------------------------------------------------------------------
+00000000001:
40:
00.000000000
SQL>c/MINUTE/HOUR
1*selectnumtodsinterval（100,'HOUR'）fromdual
SQL>/
NUMTODSINTERVAL（100,'HOUR'）
---------------------------------------------------------------------------
+00000000404:
00:
00.000000000
SQL>c/HOUR/YEAR
1*selectnumtodsinterval（100,'YEAR'）fromdual
SQL>/
selectnumtodsinterval（100,'YEAR'）fromdual
*
ERRORatline1:
ORA-01760:
illegalargumentforfunction
SQL>selectnumtoyminterval（100,'year'）fromdual;
NUMTOYMINTERVAL（100,'YEAR'）
---------------------------------------------------------------------------
+000000100-00
SQL>c/year/month
1*selectnumtoyminterval（100,'month'）fromdual
SQL>/
NUMTOYMINTERVAL（100,'MONTH'）
---------------------------------------------------------------------------
+000000008-04
时间的计算:
SQL>selectto_date（'1999-12-12','yyyy-mm-dd'）-to_date（'1999-12-01','yyyy-mm-dd'） from dual;
TO_DATE（'1999-12-12','YYYY-MM-DD'）-TO_DATE（'1999-12-01','YYYY-MM-DD'）
---------------------------------------------------------------------
11
--可以相减的结果为天.
SQL>c/1999-12-12/1999-01-12
1*selectto_date（'1999-01-12','yyyy-mm-dd'）-to_date（'1999-12-01','yyyy-mm-dd'）fromdual
SQL>/
TO_DATE（'1999-01-12','YYYY-MM-DD'）-TO_DATE（'1999-12-01','YYYY-MM-DD'）
---------------------------------------------------------------------
-323
--也可以为负数的
SQL>c/1999-01-12/2999-10-12
1*selectto_date（'2999-10-12','yyyy-mm-dd'）-to_date（'1999-12-01','yyyy-mm-dd'） from dual
SQL>/
TO_DATE（'2999-10-12','YYYY-MM-DD'）-TO_DATE（'1999-12-01','YYYY-MM-DD'）
---------------------------------------------------------------------
365193
下面看看INTERVALYEARTOMONTH怎么用.
SQL>createtablebb（adate,bdate,cintervalyear（9）tomonth）;
Tablecreated.
SQL>descbb;
NameNull?
Type
-----------------------------------------------------------------------------
ADATE
BDATE
CINTERVALYEAR（9）TOMONTH
SQL>insertintobbvalues（to_date（'1985-12-12','yyyy-mm-dd'）,to_date（'1984-12-01','yyyy-mm-dd'）,null）
1rowcreated.
SQL>select* from bb;
AB
------------------
C
---------------------------------------------------------------------------
12-DEC-8501-DEC-84
SQL>updatebbsetc=numtoyminterval（a-b,'year'）;
1rowupdated.
SQL>select*frombb;
AB
------------------
C
---------------------------------------------------------------------------
12-DEC-8501-DEC-84
+000000376-00
--直接将相减的天变成年了,因为我指定变成年的
SQL>selecta-b,c from bb;
A-B
----------
C
---------------------------------------------------------------------------
376
+000000376-00
SQL>insertintobbvalues（null,null,numtoyminterval（376,'month'））;
1rowcreated.
SQL>select*frombb;
ABC
--------------------------------------------------------------
12-DEC-8501-DEC-84+000000376-00
+000000031-04
SQL>insertintobbvalues（null,null,numtoyminterval（999999999,'year'））;
1rowcreated.
SQL>select* from bb;
ABC
---------------------------------------------------------------------------------------
12-DEC-8501-DEC-84+000000376-00
+000000031-04
+999999999-00
========================
INTERVALYEARTOMONTH类型2个TIMESTAMP类型的时间差别。
内部类型是182，长度是5。
其中4个字节存储年份差异，存储的时候在差异上加了一个0X80000000的偏移量。
一个字节存储月份的差异，这个差异加了60的偏移量。
SQL>ALTERTABLETestTimeStampADDEINTERVALYEARTOMONTH;
SQL>updatetestTimeStampsete=（selectinterval'5'year+interval'10'monthyearfromdual）;
已更新3行。
SQL>commit;
提交完成。
SQL>selectdump（e,16） from testTimeStamp;
DUMP（E,16）
---------------------------------------------
Typ=182Len=5:
80,0,0,5,46
Typ=182Len=5:
80,0,0,5,46
Typ=182Len=5:
80,0,0,5,46
年：
0X80000005-0X80000000=5
月：
0x46-60=10
INTERVALDAYTOSECOND数据类型
Oracle语法:
INTERVAL'{integer|integertime_expr|time_expr}'
{{DAY|HOUR|MINUTE}[（leading_precision）]
|SECOND[（leading_precision[,fractional_seconds_precision]）]}
[TO{DAY|HOUR|MINUTE|SECOND[（fractional_seconds_precision）]}]
leading_precision值的范围是0到9,默认是2.time_expr的格式为:
HH[:
MI[:
SS[.n]]]orMI[:
SS[.n]]orSS[.n],n表示微秒.
该类型与INTERVALYEARTOMONTH有很多相似的地方,建议先看INTERVALYEARTOMONTH再看该文.
范围值:
HOUR:
0to23
MINUTE:
0to59
SECOND:
0to59.999999999
eg:
INTERVAL'45:
12:
10.222'DAYTOSECOND（3）
表示:
4天5小时12分10.222秒
INTERVAL'45:
12'DAYTOMINUTE
表示:
4天5小时12分
INTERVAL'4005'DAY（3）TOHOUR
表示:
400天5小时,400为3为精度,所以"DAY（3）",注意默认值为2.
INTERVAL'400'DAY（3）
表示:
400天
INTERVAL'11:
12:
10.2222222'HOURTOSECOND（7）
表示:
11小时12分10.2222222秒
INTERVAL'11:
20'HOURTOMINUTE
表示:
11小时20分
INTERVAL'10'HOUR
表示:
10小时
INTERVAL'10:
22'MINUTETOSECOND
表示:
10分22秒
INTERVAL'10'MINUTE
表示:
10分
INTERVAL'4'DAY
表示:
4天
INTERVAL'25'HOUR
表示:
25小时
INTERVAL'40'MINUTE
表示:
40分
INTERVAL'120'HOUR（3）
表示:
120小时
INTERVAL'30.12345'SECOND（2,4）
表示:
30.1235秒,因为该地方秒的后面精度设置为4,要进行四舍五入.
INTERVAL'20'DAY-INTERVAL'240'HOUR=INTERVAL'10-0'DAYTOSECOND
表示:
20天-240小时=10天0秒
==================
INTERVALDAYTOSECOND类型存储两个TIMESTAMP之间的时间差异，用日期、小时、分钟、秒钟形式表示。
该数据类型的内部代码是183，长度位11字节：
l4个字节表示天数（增加0X80000000偏移量）
l小时、分钟、秒钟各用一个字节表示（增加60偏移量）
l4个字节表示秒钟的小时差异（增加0X80000000偏移量）
以下是一个例子：
SQL>altertabletestTimeStampaddfintervaldaytosecond;
表已更改。
SQL>updatetestTimeStampsetf=（selectinterval'5'day+interval'10'secondfromdual）;
已更新3行。
SQL>commit;
提交完成。
SQL>selectdump（f,16） from testTimeStamp;
DUMP（F,16）
--------------------------------------------------------------------------------
Typ=183Len=11:
80,0,0,5,3c,3c,46,80,0,0,0
Typ=183Len=11:
80,0,0,5,3c,3c,46,80,0,0,0
Typ=183Len=11:
80,0,0,5,3c,3c,46,80,0,0,0
日期：
0X80000005-0X80000000=5
小时：
60-60=0
分钟：
60-60=0
秒钟：
70-60=10
秒钟小数部分：
0X80000000-0X80000000=0
selectdate'2010-01-01'+interval'21'yearfromdual
selectdate'2010-01-01'+interval'123-2'year（4）tomonthfromdual
selectdate'2010-01-01'+interval'100'monthfromdual
selectdate'2010-01-01'+interval'100'day
（2）fromdual
selectdate'2010-01-01'+interval'12'daytohourfromdual
selectdate'2010-01-01'+interval'12:
3'daytominutefromdual
selectdate'2010-01-01'+interval'12:
3:
4'daytosecondfromdual
selectdate'2010-01-01'+interval'1:
2:
3'hourfromdual
selectdate'2010-01-01'+interval'1:
2'hourtominutefromdual
selectdate'2010-01-01'+interval'1:
2:
3'hourtosecondfromdual
selectdate'2010-01-01'+interval'1:
2'minutetosecondfromdual
转换函数：
selectnumtoyminterval（13,'month'）,numtodsinterval（100,'hour'）fromdual
PLSQL中的定义及调用：
declare
v_dtsintervaldaytosecond:
=interval'12:
3:
4'daytosecond;
v_dddate:
=date'2010-01-01';
begin
executeimmediate'altersessionsetnls_date_format=''yyyy-mm-ddhh24:
mi:
ss''';
selectv_dd+v_dtsintov_ddfromdual;
dbms_output.put_line（v_dd）;
end;
升级会员 