Two methods of converting milliseconds to timestamp types in Oracle

In many scenarios, developers are accustomed to using the number of milliseconds since the 1970-01-01 00:00:00.000 to represent a specific time, so that data can be stored in the database in numbers type, and at some point convenient to compare, again, sometimes we need to Converting this number of milliseconds into a standard timestamp type now summarizes two implementations:

Method One:
SELECT to_timestamp (' 1970-01-01 00:00:00.000 ', ' yyyy-mm-dd hh24:mi:ss.ff3 ') +1397457489296/1000/60/60/24 from dual;
This method is the simplest, the number of days to add the way, the efficiency is relatively high, but tested, will lose the millisecond part of the precision, if the millisecond accuracy is not required, you can use this method.

Method Two:
This approach is complex and usually requires the creation of a function, but the precision of the millisecond level can be preserved!

CREATE OR REPLACE FUNCTION milliseconds2timestamp (i_milliseconds number)
/***************************************************************************************
Name: Milliseconds2timestamp
Function: Converts the number of milliseconds since 1970-01-01 00:00:00 to the corresponding timestamp time type, precisely preserving the millisecond accuracy!
Parameter: I_milliseconds number of milliseconds to convert
Example: Select Milliseconds2timestamp (1397457489296) from dual;
*************************************************************************************/
RETURN TIMESTAMP as
V_timestampstr VARCHAR2 (17);
BEGIN
SELECT To_char (To_timestamp (' 1970-01-01 ', ' yyyy-mm-dd ') +
TRUNC (I_milliseconds-
(MOD (I_milliseconds-
(MOD (I_milliseconds-
MOD (i_milliseconds, 1000))/1000,
* + + MOD (i_milliseconds, 1000))/1000/60,
60) * 60 * 1000 +
MOD ((I_milliseconds-mod (i_milliseconds, 1000))/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,
* + + MOD (i_milliseconds, 1000))/1000/60,
60) * 60 * 1000 +
MOD ((I_milliseconds-mod (i_milliseconds, 1000))/1000,
* + + MOD (i_milliseconds, 1000))/1000/60/60,
24),
2,
0) | | --Hours
Lpad (MOD (I_milliseconds-
(MOD ((I_milliseconds-mod (i_milliseconds, 1000))/1000,
* + + MOD (i_milliseconds, 1000))/1000/60,
60),
2,
0) | | --Minutes
Lpad (MOD (I_milliseconds-mod (i_milliseconds, 1000)/1000, 60),
2,
0) | | --seconds
Lpad (MOD (i_milliseconds, 1000), 3, 0)--ms
Into V_timestampstr
From DUAL;
RETURN To_timestamp (v_timestampstr, ' yyyymmddhh24missff3 ');
EXCEPTION
When OTHERS Then
RETURN NULL;
END;

Two ways to convert milliseconds to timestamp types in Oracle