前言

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

1.Thinking 1 精确到秒 TO_DATE()

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

(资料图片)

--1559059199000SELECT 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 * 1000FROM DUAL;

2019-05-29 00:01:00

--1559059260000SELECT 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 * 1000FROM DUAL;

两数相减

--61000select  1559059260000-1559059199000 from dual;

缺点

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

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

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

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

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

out2019-06-06 14:13:00.000000000

模拟日期相减2019-06-06 14:13:0.1002019-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;

out0 0:2:59.9

缺点

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

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

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

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

缺点

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

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 INTEGERAS    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​​​

附图：小蓬草