前言

實際業(yè)務(wù)經(jīng)常遇到求平均響應(yīng)時間等操作，理論上應(yīng)該可以直接求日期格式毫秒值，便可以計算時間差，But，Oracle沒有對應(yīng)函數(shù)，同時網(wǎng)上的方法求得是日期格式化的double類型數(shù)據(jù)，相加減時，遵循的是十進制，日期格式為60進制，所以毫秒值并不和我們理解的一樣，目前提出幾個解決思路，但是SQL稍微有點長，以后想辦法優(yōu)化，應(yīng)該有其他思路，歡迎指正。目前解決思路就是自定義函數(shù)Thinking 4

1.Thinking 1 精確到秒 TO_DATE()

趕時間直接看??4.Thinking 4 自定義函數(shù)解析時間??,有時間的慢慢看，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;

兩數(shù)相減

--61000select  1559059260000-1559059199000 from dual;

缺點

通過獲取當(dāng)前時間，直接日期相減，獲得當(dāng)天到1970年的毫秒值，but，to_date函數(shù)只能精確到 秒，誤差會出現(xiàn)，無法精確到毫秒，但是可以精確到秒求得我們所需毫秒值

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

??實時計算日期函數(shù)TO_TIMESTAMP使用鏈接??

既然to_date函數(shù)只能精確到秒，那么，我們使用Oracle的另一個可以精確到 毫秒的函數(shù) 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

缺點

算出來為時間類型，幾分幾秒，很精準(zhǔn)的算法。 如果你需要時間展示為毫秒形式long類型，使用Thinking 4

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

換一個函數(shù)繼續(xù)做毫秒問題?oracle中 extract()函數(shù)----用于截取年、月、日、時、分、秒 詳解鏈接??

--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 自定義函數(shù)解析時間

因為實在沒有辦法，所以決定自定義時間解析，Thinking 2出的時間類型 解析成毫秒注：自定義函數(shù)可以隨數(shù)據(jù)庫一直存在，使用時調(diào)用即可 ?TestSQL我們解析Thinking 2返回的這個 0 0:2:59.9時間，然后把它截取字符串，我選擇一個比較全的時間進行測試，然后把這個SQL轉(zhuǎn)化為函數(shù) 傳參式

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;

自定義函數(shù)編寫

創(chuàng)建函數(shù)

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;

調(diào)用函數(shù)

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

順便測試一下自己的時間

注：也可以單獨計算當(dāng)前時間毫秒值，開始時間傳值為1970年毫秒日期就行

至此，終于解決了日期時間差毫秒值問題 ???祝你幸福

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

附圖：小蓬草