Oracle SQL 两个日期类型毫秒值求差，日期转毫秒，时间差（ms）自定义函数解决毫秒差

推荐原创

实际业务经常遇到求平均响应时间等操作，理论上应该可以直接求日期格式毫秒值，便可以计算时间差，But，Oracle没有对应函数，同时网上的方法求得是日期格式化的double类型数据，相加减时，遵循的是十进制，日期格式为60进制，所以毫秒值并不和我们理解的一样，目前提出几个解决思路，但是SQL稍微有点长，以后想办法优化，应该有其他思路，欢迎指正。目前解决思路就是自定义函数Thinking 4

1.Thinking 1 精确到秒 TO_DATE()

赶时间直接看4.Thinking 4 自定义函数解析时间,有时间的慢慢看，4为解决办法
分三段写；
2019-05-28 23:59:59

--1559059199000
SELECT TO_NUMBER(TO_DATE('2019-05-28 23:59:59', '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;

2019-05-29 00:01:00

--1559059260000
SELECT TO_NUMBER(TO_DATE('2019-05-29 00:01:00', '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;

--61000
select 1559059260000-1559059199000 from dual;

通过获取当前时间，直接日期相减，获得当天到1970年的毫秒值，but，to_date 函数只能精确到秒，误差会出现，无法精确到毫秒，但是可以精确到秒求得我们所需毫秒值

2.Thinking 2 精确到毫秒 TO_TIMESTAMP()

实时计算日期函数TO_TIMESTAMP使用链接

既然to_date函数只能精确到秒，那么，我们使用Oracle的另一个可以精确到毫秒的函数 to_timestamp
2019-06-06 14:13:00

--2019-06-06 14:13:00.000000000
select to_timestamp('2019-06-06 14:13:00', 'YYYY-MM-DD HH24:MI:SS.ff') from dual;

out
2019-06-06 14:13:00.000000000

模拟日期相减
2019-06-06 14:13:0.100
2019-06-06 14:10:0.200

select 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') from dual;

out
0 0:2:59.9

算出来为时间类型，几分几秒，很精准的算法。
如果你需要时间展示为毫秒形式long类型，使用Thinking 4

3.Thinking 3 精确到毫秒 EXTRACT()

换一个函数继续做毫秒问题
oracle中 extract() 函数----用于截取年、月、日、时、分、秒详解链接

--4200
select EXTRACT(SECOND FROM(to_timestamp('2019-06-06 14:12:4.200', 'YYYY-MM-DD HH24:MI:SS.ff') )) * 1000 from dual;

这个只能计算到秒，两个时间差如果大于59秒，涉及到分钟，还是没办法，就算计算分钟，它也只会显示分钟，之后精度丢失
out
4200

4 Thinking 4 自定义函数解析时间

因为实在没有办法，所以决定自定义时间解析，Thinking 2出的时间类型解析成毫秒
注：自定义函数可以随数据库一直存在，使用时调用即可 ?
TestSQL
我们解析Thinking 2返回的这个 0 0:2:59.9时间，然后把它截取字符串，我选择一个比较全的时间进行测试，然后把这个SQL转化为函数传参式

select day* 24 * 60 * 60 * 1000+ hour * 60 * 60 * 1000 + min * 60 * 1000 + ss * 1000 + mi from (
select INSTR( '31 11:33:40.89', ' ' ) konggeIndex,SUBSTR('31 11:33:40.89',1,2) testIndex,
instr('31 11:33:40.89', '.', 1) - instr('31 11:33:40.89', ':', 1,2) - 1 secondIndex,
instr('31 11:33:40.89', ':', 1,2) - instr('31 11:33:40.89', ':', 1)-1 minuteIndex,
instr('31 11:33:40.89', ':', 1) - instr('31 11:33:40.89', ' ', 1)-1 hourIndex,
instr('31 11:33:40.89', ' ', 1) -1 dayIndex,
to_number(SUBSTR( '31 11:33:40.89', INSTR( '31 11:33:40.89', '.') + 1, 3)) mi,
to_number(SUBSTR( '31 11:33:40.89', INSTR( '31 11:33:40.89', ':',1,2) + 1, instr('31 11:33:40.89', '.', 1) - instr('31 11:33:40.89', ':', 1,2) - 1)) ss,
to_number(SUBSTR( '31 11:33:40.89', INSTR( '31 11:33:40.89', ':',1,1) + 1, instr('31 11:33:40.89', ':', 1,2) - instr('31 11:33:40.89', ':', 1)-1 )) min,
to_number(SUBSTR( '31 11:33:40.89', INSTR( '31 11:33:40.89', ' ') + 1 , instr('31 11:33:40.89', ':', 1) - instr('31 11:33:40.89', ' ', 1)-1) )hour,
to_number(SUBSTR( '31 11:33:40.89' , 1, (instr('31 11:33:40.89', ' ') ) )) day
from dual) time;

自定义函数编写

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;

out