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