前言
实际业务经常遇到求平均响应时间等操作，理论上应该可以直接求日期格式毫秒值，便可以计算时间差，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_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
顺便测试一下自己的时间
注：也可以单独计算当前时间毫秒值，开始时间传值为1970年毫秒日期就行
至此，终于解决了日期时间差毫秒值问题 ???祝你幸福
送你一首歌：《The Nights》 Avicii / RAS
附图：小蓬草
