Converts data from date type to Java long type in Oracle in milliseconds

In Java Development, we often use the long type to save the time for convenience. currenttimemillis () or Java. util. date. gettime (). The value is the number of milliseconds between the current date and the 1970-01-01;

However, in Oracle, functions related to obtaining the number of milliseconds in the current time are not provided by default. Therefore, to obtain the number of milliseconds in SQL, you can only manually calculate the number as follows:

1. SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') current_date, (sysdate-to_date ('2017-01-01 ', 'yyyy-mm-dd') * 86400000 current_milli from dual;
2. Current_date current_milli
3. --------------------------------
4. 14:00:09 1231250409000

Sorry, the last time I wrote this article, I used it only to calculate the number of milliseconds in Oracle and keep it in Oracle. I didn't get the conversion in Java, so I didn't perform a detailed test, as a result, some friends who read my blog asked the mismatch question. Now I have tested it, it should beProblems caused by Time Zone: For example, I use GMT + 08 Beijing time, so the number of milliseconds calculated from Oracle in the above method will be 8 h larger than the normal date after conversion in Java;

Use the time zone of your system to slightly modify the SQL statement:
SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') current_date,
2 (sysdate-8/24-to_date ('1970-01-01 ', 'yyyy-mm-dd') * 1970 current_milli
3 from dual;

Current_date current_milli
--------------------------------
17:33:17 1251192797000

Use a simple Java test code to test and the result shows a full match. The Java code is as follows:
Date = new date (1251192797000l); // 17:33:17 <br/> system. Out. println (date );