-------------------------------------------- -----------
04-12月-10 10.40.00.265000 上午 +08:00 2010-12-5 1
解决隐式转换问题（仍保持timestamp类型）
SQL> select systimestamp ts,systimestamp+numtodsinterval(1,'day') dt from dual;
TS DT
-------------------------------------------- ----------------------------------------------
04-12月-10 10.42.46.515000 上午 +08:00 05-12月-10 10.42.46.515000000 上午 +08:00
两个timestamp相减
SQL> select dt2 - dt1
2 from (select to_timestamp('29-2-2000 01:02:03.122000',
3 'dd-mm-yyyy hh24:mi:ss.ff') dt1,
4 to_timestamp('15-3-2001 11:22:33.000000',
5 'dd-mm-yyyy hh24:mi:ss.ff') dt2
6 from dual);
DT2-DT1
---------------------------------------
+000000380 10:20:29.878000000
将其转换为具体的年、天时分秒
SQL> select numtoyminterval(months_between(dt2, dt1), 'month') years_months,
2 dt2 - add_months(dt1, trunc(months_between(dt2, dt1))) days_hours
3 from (select to_timestamp('29-2-2000 01:02:03.122000',
4 'dd-mm-yyyy hh24:mi:ss.ff') dt1,
5 to_timestamp('15-3-2001 11:22:33.000000',
6 'dd-mm-yyyy hh24:mi:ss.ff') dt2
7 from dual);
YEARS_MONTHS DAYS_HOURS
--------------------------------------- ---------------------------------------
+000000001-00 +000000015 10:20:30.000000000
注：to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型
SQL> select trunc(to_date('20100908','yyyymmdd') - to_date('20100907','yyyymmdd')) m from dual;
M
----------
1
--两个相减的timestamp类型不能用trunc
SQL> select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual;
select trunc(to_timestamp('20100908','yyyymmdd') - to_timestamp('20100907','yyyymmdd')) m from dual
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND
to_char的使用：
SQL> SELECT sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff3') FROM dual;
SYSDATE SYSTIMESTAMP TO_CHAR(SYSTIMESTAMP,'YYYYMMDD'
----------- -------------------------------------------------------- ------------------------------
2010-12-2 1 02-12月-10 02.06.34.046000 下午 +08:00 20101202 14:06:34.046
对于 timestamp类型的数据直接相减可以精确到秒以后
SQL> select systimestamp-systimestamp from dual;
SYSTIMESTAMP-SYSTIMESTAMP
---------------------------------------
+000000000 00:00:00.000000
三、interval year to month 类型的使用
interval year to month 可以用numtoyminterval或to_yminterval函数替代
SQL> select numtoyminterval(5,'year') + numtoyminterval(2,'month') from dual;
NUMTOYMINTERVAL(5,'YEAR')+NUMT
---------------------------------------
+000000005-02
SQL> select numtoyminterval(5*12+2,'month') from dual;
NUMTOYMINTERVAL(5*12+2,'MONTH'
---------------------------------------
+000000005-02
SQL> select to_yminterval('5-2') from dual;
TO_YMINTERVAL('5-2')
---------------------------------------
+000000005-02
SQL> select interval '5-2' year to month from dual;
INTERVAL'5-2'YEARTOMONTH
----------------------------------