格式化输出interval

Question

使用NUMTODSINTERVAL( date-sysdate ,'Day') 得到interval， DATE-SYSDATE
 ------------------------------
 +000000337 00:03:46.000000000
 +000001428 00:03:46.000000000
 -000000027 23:56:14.000000000
 +000000010 00:03:45.999999999
 +000000036 00:03:45.999999999
 +000000646 00:03:46.000000000
 +000000646 00:03:46.000000000请问怎么才能格式化成 ------------------------------
 +337 Day 00:03:46
 .....etc的样式

Answer 1

-- *2) 得到两个日期之差 ( P531 )
col month_btwn for a30;
col days for a35;
col months for a35;
select dt2-dt1,
       months_between(dt2,dt1) months_btwn,
       numtodsinterval(dt2-dt1,'day') days,
       numtoyminterval(months_between(dt2,dt1),'month') months
from (select to_date('2000-02-29 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
             to_date('2001-03-15 11:22:33','yyyy-mm-dd hh24:mi:ss') dt2
        from dual );   DT2-DT1 MONTHS_BTWN DAYS                                MONTHS
---------- ----------- ----------------------------------- -----------------------------------
380.430903  12.5622872 +000000380 10:20:30.000000000       +000000001-00select numtoyminterval(months_between(dt2,dt1),'month') year_months,
       numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1)) ), 'day' ) days_hours
from (select to_date('2000-02-29 01:02:03','yyyy-mm-dd hh24:mi:ss') dt1,
             to_date('2001-03-15 11:22:33','yyyy-mm-dd hh24:mi:ss') dt2
        from dual );YEAR_MONTHS                                                                 DAYS_HOURS
--------------------------------------------------------------------------- ----------------------------------
+000000001-00                                                               +000000015 10:20:30.000000000

Answer 2

to_number(NUMTODSINTERVAL( date-sysdate ,'Day'))