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

In many scenarios, developers often use the millisecond value from 1970-01-0100:00:00. 000 to indicate the specific time, so that data can be stored in the database as NUMBER.

In many scenarios, developers are used to express the specific time in milliseconds since 00:00:00. 000, so that data can be stored in the database as numbers.

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, and the efficiency is relatively high by adding the number of days. However, after testing, the precision of the millisecond part will be lost. If there is no requirement for 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;