interval函数以及numtoninterval函数的说明

一、interval函数--将数值按标准换算为日期

year(5)其中5代表精度，默认值为2，精度的长度一定要大于前面的值。

SQL> select interval '1234' year(5) from dual;

INTERVAL'1234'YEAR(5)
---------------------------------------------------------------------------
+01234-00

精度短于数值会报错：

SQL> select interval '1234' year(3) from dual;
select interval '1234' year(3) from dual
                *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

133/12=11.08，取整后为11年1月

SQL> select interval '133' month  from dual;

INTERVAL'133'MONTH
---------------------------------------------------------------------------
+11-01

二、numtodsinterval、numtodsinterval函数,将数字转成年月，时分秒

numtodsinterval只转换year,month 

SQL> select numtoyminterval(133, 'year') from dual;

NUMTOYMINTERVAL(133,'YEAR')
---------------------------------------------------------------------------
+000000133-00

SQL> select NUMTOYMINTERVAL(133,'MONTH') from dual;

NUMTOYMINTERVAL(133,'MONTH')
---------------------------------------------------------------------------
+000000011-01

numtodsinterval只转换day,hour,moinut,second

SQL> select numtodsinterval(133, 'day') from dual; --133转换为第133天

NUMTODSINTERVAL(133,'DAY')
---------------------------------------------------------------------------
+000000133 00:00:00.000000000


SQL> select numtodsinterval(133, 'hour') from dual;--133转换为5天13小时

NUMTODSINTERVAL(133,'HOUR')
---------------------------------------------------------------------------
+000000005 13:00:00.000000000


SQL> select numtodsinterval(133, 'minute') from dual; --133转换为2小时13分钟

NUMTODSINTERVAL(133,'MINUTE')
---------------------------------------------------------------------------
+000000000 02:13:00.000000000


SQL> select numtodsinterval(133, 'second') from dual; --133转换为2分13秒

NUMTODSINTERVAL(133,'SECOND')
---------------------------------------------------------------------------
+000000000 00:02:13.000000000

三、创建Interval分区示例：

interval(numtoyminterval(1,'MONTH'))表示range的取值范围为个自然月

CREATE TABLE t
(
  id number,
  create_date  date
)
partition by range(create_date) interval(numtoyminterval(1,'MONTH'))
(
  partition p201303 values less than (date '2013-03-01'),
  partition p201304 values less than (date '2013-04-01'),
  partition p201305 values less than (date '2013-05-01')
);