oracle中的INTERVAL函数详解.docx

1、oracle中的INTERVAL函数详解oracle中的INTERVAL函数详解oracle中的INTERVAL函数详解INTERVAL YEAR TO MONTH数据类型Oracle语法:INTERVAL integer - integer YEAR | MONTH (precision)TO YEAR | MONTH该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2.eg:INTERVAL 123-2 YEAR(3) TO MONTH表示: 123年2个月, YEAR(3) 表示年的精度为3, 可见123刚好为

2、3为有效数值, 如果该处YEAR(n), n select numtodsinterval(100,DAY) from dual;NUMTODSINTERVAL(100,DAY)-+000000100 00:00:00.000000000SQL c/DAY/SECOND1* select numtodsinterval(100,SECOND) from dualSQL /NUMTODSINTERVAL(100,SECOND)-+000000000 00:01:40.000000000SQL c/SECOND/MINUTE1* select numtodsinterval(100,MINUTE)

3、 from dualSQL /NUMTODSINTERVAL(100,MINUTE)-+000000000 01:40:00.000000000SQL c/MINUTE/HOUR1* select numtodsinterval(100,HOUR) from dualSQL /NUMTODSINTERVAL(100,HOUR)-+000000004 04:00:00.000000000SQL c/HOUR/YEAR1* select numtodsinterval(100,YEAR) from dualSQL /select numtodsinterval(100,YEAR) from dua

4、l*ERROR at line 1:ORA-01760: illegal argument for functionSQL select numtoyminterval(100,year) from dual;NUMTOYMINTERVAL(100,YEAR)-+000000100-00SQL c/year/month1* select numtoyminterval(100,month) from dualSQL /NUMTOYMINTERVAL(100,MONTH)-+000000008-04时间的计算:SQL select to_date(1999-12-12,yyyy-mm-dd) -

5、 to_date(1999-12-01,yyyy-mm-dd)fromdual;TO_DATE(1999-12-12,YYYY-MM-DD)-TO_DATE(1999-12-01,YYYY-MM-DD)-11- 可以相减的结果为天.SQL c/1999-12-12/1999-01-121* select to_date(1999-01-12,yyyy-mm-dd) - to_date(1999-12-01,yyyy-mm-dd) from dualSQL /TO_DATE(1999-01-12,YYYY-MM-DD)-TO_DATE(1999-12-01,YYYY-MM-DD)-323- 也可

6、以为负数的SQL c/1999-01-12/2999-10-121* select to_date(2999-10-12,yyyy-mm-dd) - to_date(1999-12-01,yyyy-mm-dd)fromdualSQL /TO_DATE(2999-10-12,YYYY-MM-DD)-TO_DATE(1999-12-01,YYYY-MM-DD)-365193下面看看INTERVAL YEAR TO MONTH怎么用.SQL create table bb(a date, b date, c interval year(9) to month);Table created.SQL d

7、esc bb;Name Null? Type- - -A DATEB DATEC INTERVAL YEAR(9) TO MONTHSQL insert into bb values(to_date(1985-12-12, yyyy-mm-dd), to_date(1984-12-01,yyyy-mm-dd), null)1 row created.SQL select *frombb;A B- -C-12-DEC-85 01-DEC-84SQL update bb set c = numtoyminterval(a-b, year);1 row updated.SQL select * fr

8、om bb;A B- -C-12-DEC-85 01-DEC-84+000000376-00- 直接将相减的天变成年了, 因为我指定变成年的SQL select a-b, cfrombb;A-B-C-376+000000376-00SQL insert into bb values(null,null,numtoyminterval(376,month);1 row created.SQL select * from bb;A B C- - -12-DEC-85 01-DEC-84 +000000376-00+000000031-04SQL insert into bb values ( nu

9、ll,null, numtoyminterval(999999999,year);1 row created.SQL select *frombb;A B C- - -12-DEC-85 01-DEC-84 +000000376-00+000000031-04+999999999-00=INTERVAL YEAR TO MONTH类型2个TIMESTAMP类型的时间差别。内部类型是182，长度是5。其中4个字节存储年份差异，存储的时候在差异上加了一个0X80000000的偏移量。一个字节存储月份的差异，这个差异加了60的偏移量。SQL ALTER TABLE TestTimeStamp ADD

10、 E INTERVAL YEAR TO MONTH;SQL update testTimeStamp set e=(select interval 5 year + interval 10 month year from dual);已更新3行。SQL commit;提交完成。SQL select dump(e,16)fromtestTimeStamp;DUMP(E,16)-Typ=182 Len=5: 80,0,0,5,46Typ=182 Len=5: 80,0,0,5,46Typ=182 Len=5: 80,0,0,5,46年：0X80000005-0X80000000=5月：0x46-6

11、0=10INTERVAL DAY TO SECOND数据类型Oracle语法:INTERVAL integer | integer time_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的格式

12、为:HH:MI:SS.n or MI:SS.n or SS.n, n表示微秒.该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.范围值:HOUR: 0 to 23MINUTE: 0 to 59SECOND: 0 to 59.999999999eg:INTERVAL 4 5:12:10.222 DAY TO SECOND(3)表示: 4天5小时12分10.222秒INTERVAL 4 5:12 DAY TO MINUTE表示: 4天5小时12分INTERVAL 400 5 DAY(3) TO HOUR表示: 400

13、天5小时, 400为3为精度,所以DAY(3), 注意默认值为2.INTERVAL 400 DAY(3)表示: 400天INTERVAL 11:12:10.2222222 HOUR TO SECOND(7)表示: 11小时12分10.2222222秒INTERVAL 11:20 HOUR TO MINUTE表示: 11小时20分INTERVAL 10 HOUR表示: 10小时INTERVAL 10:22 MINUTE TO SECOND表示: 10分22秒INTERVAL 10 MINUTE表示: 10分INTERVAL 4 DAY表示: 4天INTERVAL 25 HOUR表示: 25小时I

14、NTERVAL 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 DAY TO SECOND表示: 20天 - 240小时 = 10天0秒=INTERVAL DAY TO SECOND类型存储两个TIMESTAMP之间的时间差异，用日期、小时、分钟、秒钟形式表示。该数据类型的内部代码是183，长度位11字节：l 4个字

15、节表示天数(增加0X80000000偏移量)l 小时、分钟、秒钟各用一个字节表示(增加60偏移量)l 4个字节表示秒钟的小时差异(增加0X80000000偏移量)以下是一个例子：SQL alter table testTimeStamp add f interval day to second ;表已更改。SQL update testTimeStamp set f=(select interval 5 day + interval 10 second from dual);已更新3行。SQL commit;提交完成。SQL select dump(f,16)fromtestTimeStamp

16、;DUMP(F,16)-Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0Typ=183 Len=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=0select date 2010-01-01 + interval 21 year from dualselect date 2010-01-01 +

17、 interval 123-2 year(4) to month from dualselect date 2010-01-01 + interval 100 month from dualselect date 2010-01-01 + interval 100 day(2) from dualselect date 2010-01-01 + interval 1 2 day to hour from dualselect date 2010-01-01 + interval 1 2:3 day to minute from dualselect date 2010-01-01 + inte

18、rval 1 2:3:4 day to second from dualselect date 2010-01-01 + interval 1:2:3 hour from dualselect date 2010-01-01 + interval 1:2 hour to minute from dualselect date 2010-01-01 + interval 1:2:3 hour to second from dualselect date 2010-01-01 + interval 1:2 minute to second from dual转换函数：select numtoymi

19、nterval(13, month), numtodsinterval(100, hour) from dualPLSQL中的定义及调用：declare v_dts interval day to second := interval 1 2:3:4 day to second; v_dd date := date 2010-01-01;begin execute immediate alter session set nls_date_format = yyyy-mm-dd hh24:mi:ss; select v_dd + v_dts into v_dd from dual; dbms_output.put_line(v_dd);end;