oracle中的INTERVAL函数详解(二)
SQL> c/1999-01-12/2999-10-12
1* select to_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
下面看看INTERVAL YEAR TO MONTH怎么用.
SQL> create table bb(a date, b date, c interval year(9) to month);
Table created.
SQL> desc bb;
Name Null Type
----------------------------------------- -------- ----------------------------
A DATE
B DATE
C INTERVAL YEAR(9) TO MONTH
SQL> 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 * from bb;
A B
--------- ---------
C
---------------------------------------------------------------------------
12-DEC-85 01-DEC-84
SQL> update bb set c = numtoyminterval(a-b, 'year');
1 row updated.
SQL> select * from bb;
A B
--------- ---------
C
---------------------------------------------------------------------------
12-DEC-85 01-DEC-84
+000000376-00
-- 直接将相减的天变成年了, 因为我指定变成年的
SQL> select a-b, c from bb;
A-B
----------
C
---------------------------------------------------------------------------
376
+000000376-00
SQL> 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-04
SQL> insert into bb values ( null,null, numtoyminterval(999999999,'year'));
1 row created.
SQL> select * from bb;
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 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) from testTimeStamp;
DUMP(E,16)
---------------------------------------------
Typ=182 Len=5: 80,0,0,5,46
Typ=182 Len=5: 80,0,0,5,46
Typ=182 Len=5: 80,0,0,5,46
年：0X80000005-0X80000000=5
月：0x46-60=10
INTERVAL 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的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.
范围值:
HOUR: 0 to 23
MINUTE: 0 to 59
SECOND: 0 to 59.999999999
eg:
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' DA