Oracle中怎么计算出两个timestamp类型相差的毫秒数

表中有开始时间和结束时间，两个都是timestamp类型，直接相减得到+000000000 00:00:00.513000这种类型，我想得到的数据类型为100000这样的毫秒数，求教如何写

CREATE OR REPLACE FUNCTION uf_timestamp_diff(endtime in TIMESTAMP,starttime in TIMESTAMP)

RETURN INTEGER

AS

str VARCHAR2(50);

misecond INTEGER;

seconds INTEGER;

minutes INTEGER;

hours INTEGER;

days INTEGER;

BEGIN

str:=to_char(endtime-starttime);

misecond:=to_number(SUBSTR(str,INSTR(str,' ')+10,3));

seconds:=to_number(SUBSTR(str,INSTR(str,' ')+7,2));

minutes:=to_number(SUBSTR(str,INSTR(str,' ')+4,2));

hours:=to_number(SUBSTR(str,INSTR(str,' ')+1,2));

days:=to_number(SUBSTR(str,1,INSTR(str,' ')));

RETURN days*24*60*60*1000+hours*60*60*1000+minutes*60*1000+seconds*1000+misecond;  

END;

你看看这个，就是把日期类型转换成毫秒数，进行相减
SELECT TO_NUMBER(TO_DATE('2014-07-28 17:12:45', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000
FROM DUAL;

http://www.jb51.net/article/53007.htm

数据类型 timestamp 例如20-JUL-17 03.27.32.704000 PM

create or replace function oracle_to_unix(in_date IN DATE) return number is

begin

return( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600);

end oracle_to_unix;

Oracle date时间换成Unix时间戳

--相差天数
select to_number(
(to_date(to_char(b,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
-
(to_date(to_char(a,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
) from a;
--相差小时
select to_number(
(to_date(to_char(b,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
-
(to_date(to_char(a,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
)*24 from a;
--相差分钟
select to_number(
(to_date(to_char(b,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
-
(to_date(to_char(a,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
)*24*60 from a;
--相差秒
select to_number(
(to_date(to_char(b,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
-
(to_date(to_char(a,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
)*24*60*60 from a;
--相差毫秒
select to_number(
(to_date(to_char(b,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
-
(to_date(to_char(a,'yyyy-mm-dd HH:mi:ss'),'yyyy-mm-dd HH:mi:ss'))
)*24*60*60*1000 from a;