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

顺便测试一下自己的时间

注：也可以单独计算当前时间毫秒值，开始时间传值为1970年毫秒日期就行

至此，终于解决了日期时间差毫秒值问题 ???祝你幸福

送你一首歌：​​《The Nights》 Avicii / RAS​​​

附图：小蓬草