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

Question

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

Answer 1

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;