oracel中两个timestamp差值的有关问题_Oracle技术

oracel中两个timestamp差值的问题?
oracel中两个timestamp差值用毫秒,秒表示...
谢谢. SELECT A.E - A.S FROM T A

怎样用 A.E - A.S 表示成毫秒和秒.

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

SQL code

--用秒表示两个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;