oracle TIMESTAMP日期相减

 select extract(day from inter) * 24 * 60 * 60 +  
 extract(hour from inter) * 60 * 60 + extract(minute from inter) * 60 +  
 extract(second from inter) "seconds" 
 from (select to_timestamp('20100115112233', 'yyyymmddhh24miss') -  
         to_timestamp('20100101123456', 'yyyymmddhh24miss') inter  
    from dual); 

 

WITH a AS
(SELECT to_timestamp('2010-7-7 11:11:11', 'yyyy-mm-dd hh24:mi:ss') - to_timestamp('2010-7-5 12:12:12', 'yyyy-mm-dd hh24:mi:ss') inter
FROM dual)
SELECT inter, extract(DAY FROM inter), extract(hour FROM inter), extract(minute FROM inter), extract(SECOND FROM inter) FROM a;
其他参考： http://xsb.itpub.net/post/419/52334