Shulou(Shulou.com)05/31 Report--

This article will explain in detail how Oracle calculates the time difference in milliseconds. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

In Oracle, we know that TO_DATE function can be used to calculate the time difference of seconds, minutes, hours, days, months, years, weeks, etc., but milliseconds are not easy to calculate. TO_DATE function can only be accurate to seconds, while milliseconds can only use TO_TIMESTAMP function. However, the difference directly subtracted by this function like TO_DATE is NUMBER type, and an error will be reported if you convert it with TO_NUMBER function.

Here I calculate the millisecond part separately with a separated string. If there is a better way, please share it. The following is a template with a millisecond query time difference (Field 1-Field 2):

SELECT ((TO_TIMESTAMP (field 1, 'YYYY-MM-DD HH24:MI:SS.FF9') + 0)-(TO_TIMESTAMP (field 2,' YYYY-MM-DD HH24:MI:SS.FF9') + 0) * 24 * 60 * 60 + TO_NUMBER ('0' | | (TO_NUMBER (SUBSTR (field 1, INSTR (field 1,'.)-TO_NUMBER (SUBSTR (field 2, INSTR (field 2) '.) * 1000FROM table name

Here is the difference in milliseconds. Delete the "() * 1000" section if you need to display the decimal seconds. Here is a test code that runs directly:

SELECT (TO_TIMESTAMP ('2016-04-13 17 YYYY-MM-DD HH24:MI:SS.FF9') + 0)-(TO_TIMESTAMP (' 2016-04-13 17 YYYY-MM-DD HH24:MI:SS.FF9') + 0)) * 24 * 60 * 60 + TO_NUMBER ('0' | (TO_NUMBER (SUBSTR ('2016-04-13) 1717 13 YYYY-MM-DD HH24:MI:SS.FF9') 50.998' INSTR ('2016-04-13 17SUBSTR 13RV 50.998mm,'.'))-TO_NUMBER (SUBSTR ('2016-04-1317SUBSTR 13RV 47.235mm, INSTR (' 2016-04-1317SUBSTR 13RV 50.235mm,'.) * 1000FROM DUAL

The result is: 3763

This is the end of the article on "how to calculate the time difference in milliseconds by Oracle". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.