--用秒表示两个timestamp类型之差
SQL> select day*24*60*60+hour*60*60+minute*60+second second from
  2  (select extract(day from edt-sdt) day,
  3          extract(hour from edt-sdt) hour,
  4          extract(minute from edt-sdt) minute,
  5          extract(second from edt-sdt) second
  6  from (select to_timestamp('2011-05-30 07:12:21.126000','yyyy-mm-dd hh24:mi:ss.ff') sdt,
  7               to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss.ff') edt
  8        from dual))
  9  /
 
    SECOND
----------
105217.874

------解决方案--------------------
好像只能用字符串截取出来，然后换算得到你要的时间单位，比较麻烦

------解决方案--------------------

SQL code

--用毫秒表示：
SQL> select day*24*60*60*1000+hour*60*60*1000+minute*60*1000+second*1000 Millisecond from
  2  (select extract(day from edt-sdt) day,
  3          extract(hour from edt-sdt) hour,
  4          extract(minute from edt-sdt) minute,
  5          extract(second from edt-sdt) second
  6  from (select to_timestamp('2011-05-30 07:12:21.126000','yyyy-mm-dd hh24:mi:ss.ff') sdt,
  7               to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss.ff') edt
  8        from dual))
  9  /
 
MILLISECOND
-----------
  105361874

------解决方案--------------------

--精确到秒
SELECT ((A.E+0) - (A.S+0))*86400 FROM T A;
SELECT (cast(A.E as date) - cast(A.S as date))*86400 FROM T A;

------解决方案--------------------

SQL code
--精确到毫秒
select substr(a.e - a.s, 1, 10)*86400000+
       substr(a.e - a.s, 12, 2)*3600000+
       substr(a.e - a.s, 15, 2)*60000+
       substr(a.e - a.s, 18, 2)*1000+
       substr(a.e - a.s, 21,3)
  FROM t a;