In Java, System.currenttimemillis () takes the number of milliseconds from 1970-01-01 00:00:00.000 to the current time, a long value.
This value is now accessed in a table in the Oracle database and needs to be converted to a readable date type.
Current Millis provides conversion results for timemillis and some common conversion methods.
But the number that is obtained on PL/SQL is accurate to thousands of milliseconds, not exactly to milliseconds.
A package was organized to convert Currenttimemillis to date.
Note: System.currenttimemillis () takes the time of the current time zone, so you need to be aware of time zone conversions when converting.
Select sessiontimezone,dbtimezone,sysdate,current_date from dual;
CREATE OR REPLACEPackage cux_time_utils_pkg is FUNCTIONUnix_timestamp_to_utc_date (In_unix_timestampinch Number)RETURNDATE deterministic; FUNCTIONUnix_timestamp_to_session_date (In_unix_timestampinch Number)RETURNDATE deterministic; FUNCTIONDate_to_unix_timesecond (in_dateinchDATE)RETURN Numberdeterministic; FUNCTIONDate_to_unix_times_millis (in_dateinchDATE)RETURN Numberdeterministic; FUNCTIONJava_currenttimemillisRETURN Number; FUNCTIONCurrent_unix_times_millisRETURN Numberdeterministic;END;
CREATE OR REPLACEPackage BODY cux_time_utils_pkg is --return utc0 Date --returns the time of 0 time zone FUNCTIONUnix_timestamp_to_utc_date (In_unix_timestampinch Number)RETURNDATE Deterministic isv_date date; BEGINv_date:=To_date ('1970-01-01', 'YYYY-MM-DD')+In_unix_timestamp/ + / - / - / -; RETURNv_date; END; --return Sessiontimezone Date --returns the time zone in the current callback --select Sessiontimezone,dbtimezone,sysdate,current_date from dual; FUNCTIONUnix_timestamp_to_session_date (In_unix_timestampinch Number)RETURNDATE Deterministic isv_date date; BEGINv_date:=To_date ('1970-01-01', 'YYYY-MM-DD')+In_unix_timestamp/ + / - / - / - +To_number (substr (Tz_offset (sessiontimezone),1, 3))/ -; RETURNv_date; END; --returns the number of seconds between 1970-01-01 00:00:00 and In_date FUNCTIONDate_to_unix_timesecond (in_dateinchDATE)RETURN NumberDeterministic isV_unix_timestamp Number; BEGINV_unix_timestamp:=(in_date-To_date ('1970-01-01', 'YYYY-MM-DD'))* - * - * -; RETURNV_unix_timestamp; END; --returns the number of milliseconds between 1970-01-01 00:00:00 and In_date --This method is not precise enough because the in_date precision is seconds, so the precision of the return value is thousand milliseconds --adjustable entry structure for timestamp, then optimized FUNCTIONDate_to_unix_times_millis (in_dateinchDATE)RETURN NumberDeterministic isV_unix_timestamp Number; BEGINV_unix_timestamp:=(in_date-To_date ('1970-01-01', 'YYYY-MM-DD'))* + * - * - * -; RETURNV_unix_timestamp; END; --returns the current number of milliseconds in Java FUNCTIONJava_currenttimemillisRETURN Number asLANGUAGE JAVA NAME'Java.lang.System.currentTimeMillis () return Java.lang.Integer'; --returns the number of milliseconds in the current time FUNCTIONCurrent_unix_times_millisRETURN NumberDeterministic isV_unix_timestamp Number; BEGINV_unix_timestamp:=Extract Day from(Systimestamp-To_timestamp ('1970-01-01', 'YYYY-MM-DD')))* + * - * - * - +To_number (To_char (SYS_EXTRACT_UTC (Systimestamp), 'SSSSSFF3')); RETURNV_unix_timestamp; END;END;
Reference:
https://currentmillis.com/
Http://stackoverflow.com/questions/2824710/oracle-equaivalent-of-java-system-currenttimemillis
Http://stackoverflow.com/questions/4312514/get-date-from-a-long-var-char-milliseconds-value-stored-in-oracle-database
Http://developer-should-know.com/post/77394226827/converting-between-milliseconds-and-dates-in
The number of milliseconds from 1970-1-1 to the current time in Java converted to Oracle date