一、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')
);