oracle sql 计算两个带毫秒的时间字符串相差

创建函数

CREATE OR REPLACE FUNCTION get_timestamp_cha(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, '.') + 1, 3));

seconds := to_number(SUBSTR(str, INSTR(str, ':',1,2) + 1, instr(str, '.', 1) - instr(str, ':', 1,2) - 1));

minutes := to_number(SUBSTR(str, INSTR(str, ':',1,1) + 1, (instr(str, ':', 1,2) )- instr(str, ':', 1)-1 ));

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

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 get_timestamp_cha(
to_timestamp('2019-06-06 14:13:0.100', 'YYYY-MM-DD HH24:MI:SS.ff'),
to_timestamp('2019-06-06 14:10:0.200', 'YYYY-MM-DD HH24:MI:SS.ff') )
millisecond
from dual;

————————————————
原文链接：https://blog.csdn.net/HezhezhiyuLe/article/details/91043272