Two Methods for converting the number of milliseconds to the timestamp type in Oracle

In many scenarios, developers prefer to use 00:00:00. the NUMBER of milliseconds since indicates the specific time. In this way, data can be stored in the database as NUMBER, which is convenient for comparison in some cases. Similarly, sometimes we need to convert the number of milliseconds to the standard TIMESTAMP type. Now we have summarized two implementation methods:

Method 1:
SELECT TO_TIMESTAMP ('1970-01-01 00:00:00. 000', 'yyyy-MM-dd hh24: mi: ss. ff3') + 1970/1397457489296/1000/24 FROM dual;
This method is the simplest. The efficiency is relatively high when the number of days is used. However, after testing, the precision of the millisecond part will be lost. If there is no requirement on the precision of the millisecond level, this method can be used.
Method 2:
This method is complex. You usually need to create a function, but you can keep the precision in milliseconds!

Create or replace function MILLISECONDS2TIMESTAMP (I _MILLISECONDS NUMBER)
/*************************************** **************************************** ********
Name: MILLISECONDS2TIMESTAMP
Function: converts the number of milliseconds since 00:00:00 to the corresponding timestamp time type, precisely retaining the precision in milliseconds!
Parameter: NUMBER of milliseconds for I _MILLISECONDS to be converted
Example: select MILLISECONDS2TIMESTAMP (1397457489296) from dual;
**************************************** **************************************** *****/
RETURN TIMESTAMP
V_TIMESTAMPSTR VARCHAR2 (17 );
BEGIN
SELECT TO_CHAR (TO_TIMESTAMP ('2017-01-01 ', 'yyyy-MM-dd') +
TRUNC (I _MILLISECONDS-
(MOD (I _MILLISECONDS-
(MOD (I _MILLISECONDS-
MOD (I _MILLISECONDS, 1000)/1000,
60) * 1000 + MOD (I _MILLISECONDS, 1000)/1000/60,
60) * 60*1000 +
MOD (I _MILLISECONDS-MOD (I _MILLISECONDS, 1000)/1000,
60) * 1000 + MOD (I _MILLISECONDS, 1000)/1000/60/60/24 ),
'Yyyymmdd') | -- Date
LPAD (MOD (I _MILLISECONDS-
(MOD (I _MILLISECONDS-
(MOD (I _MILLISECONDS-MOD (I _MILLISECONDS, 1000)/1000,
60) * 1000 + MOD (I _MILLISECONDS, 1000)/1000/60,
60) * 60*1000 +
MOD (I _MILLISECONDS-MOD (I _MILLISECONDS, 1000)/1000,
60) * 1000 + MOD (I _MILLISECONDS, 1000)/1000/60/60,
24 ),
2,
0) | -- hour
LPAD (MOD (I _MILLISECONDS-
(MOD (I _MILLISECONDS-MOD (I _MILLISECONDS, 1000)/1000,
60) * 1000 + MOD (I _MILLISECONDS, 1000)/1000/60,
60 ),
2,
0) | -- minute
LPAD (MOD (I _MILLISECONDS-MOD (I _MILLISECONDS, 1000)/1000, 60 ),
2,
0) | -- seconds
LPAD (MOD (I _MILLISECONDS, 1000), 3, 0) -- millisecond
INTO V_TIMESTAMPSTR
From dual;
RETURN TO_TIMESTAMP (V_TIMESTAMPSTR, 'yyyymmddhh24missff3 ');
EXCEPTION
WHEN OTHERS THEN
Return null;
END;