oracle提供内部函数:
numtodsinterval(<x>,<c>) ,
x是一个数字,加减值,可以为小数和分数,如1,1/2, 0.05
c是一个字符串,为加减单位 ,
常用的单位有 ('day','hour','minute','second')表明x的单位,这个函数把x转为interval day to second数据类型
------------------------------------------------------------------------------------
example
SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:45:34 2007-09-05 04:45:34
example
SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:45:34 2007-09-05 04:45:34
numtoyminterval 与numtodsinterval函数类似,将x转为interval year to month数据类型
常用的单位有'year','month'
example
SQL> select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;
SYSDATE RES
------------------- -------------------
2007-09-05 01:54:53 2010-09-05 01:54:53
------------------- -------------------
2007-09-05 01:54:53 2010-09-05 01:54:53
================================
测试sql
select systimestamp, systimestamp+NUMTODSINTERVAL(0.5,'SECOND'), systimestamp+NUMTODSINTERVAL(0.05,'SECOND') from dual;
测试结果:
24-1月 -18 04.24.10.411000 下午 +08:00
24-1月 -18 04.24.10.911000000 下午 +08:00
24-1月 -18 04.24.10.461000000 下午 +08:00