The number of milliseconds from 1970-1-1 to the current time in Java converted to Oracle date

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