日期:2014-05-16  浏览次数:20879 次

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;