Oracle 日期和毫秒互转

Oracle 日期和毫秒互转

– 当前毫秒时间戳转毫秒

SELECT SYSTIMESTAMP(3),to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 /*24 * 60 * 60 * 1000*/ + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF'))) AS milliseconds FROM DUAL;
SYSTIMESTAMP(3) MILLISECONDS
--------------------------------------- --------------------
15-DEC-22 10.54.35.272 AM +08:00 1671072875272

– 当前微秒时间戳转微秒

SELECT SYSTIMESTAMP(6),to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000000 /*24 * 60 * 60 * 1000 *1000*/ + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(6), 'FF'))) AS microseconds FROM DUAL;
SYSTIMESTAMP(6) MICROSECONDS
--------------------------------------- -------------------
15-DEC-22 10.55.06.618041 AM +08:00 1671072906618041

– 日期时间转毫秒(丢失毫秒)

SELECT TO_CHAR(TO_NUMBER(SYSDATE - TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000) MILLISECONDS FROM DUAL;
MILLISECONDS
-------------
1669678245000

– 毫秒转时间戳(字符型)忽略了隐式转换

SELECT TO_CHAR(TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS') + 1671072875272 / (1000 * 60 * 60 * 24), 'YYYY-MM-DD HH:MI:SS')||'.'||substr(1671072875272,-3) AS CHAR_Timestamp FROM DUAL;
CHAR_TIMESTAMP
-----------------------
2022-12-15 10:54:35.272

– 毫秒转时间戳(时间戳类型)

SELECT TO_TIMESTAMP(TO_CHAR(TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS') + 1671072875272 / (1000 * 60 * 60 * 24), 'YYYY-MM-DD HH:MI:SS')||'.'||substr(1671072875272,-3),'YYYY-MM-DD HH24:MI:SS.FF3') AS Trans_Timestamp FROM DUAL;
TRANS_TIMESTAMP
-------------------------------------
15-DEC-22 10.54.35.272000000 AM

– 毫秒转时间，丢失毫秒

SELECT TO_CHAR(TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS') + 1671072875272 / (1000 * 60 * 60 * 24), 'YYYY-MM-DD HH:MI:SS') AS CHAR_DATE FROM DUAL;
CHAR_DATE
-------------------
2022-12-15 10:54:35

定义函数实现毫秒转时间戳

定义后授权Public，该实例是使用 SYSTEM 用户建立函数：

CREATE PUBLIC SYNONYM MILLI2TIMESTAMPCST FOR SYSTEM.MILLI2TIMESTAMPCST;
CREATE PUBLIC SYNONYM MILLISECONDS2TIMESTAMPCST FOR SYSTEM.MILLISECONDS2TIMESTAMPCST;


使用

SELECT MILLISECONDS2TIMESTAMPCST (1671072875272) Char_Timestamp FROM DUAL;
CHAR_TIMESTAMP
---------------------------------------------------------------------------
15-DEC-22 10.54.35.272000000 AM


创建函数方法1

CREATE OR REPLACE FUNCTION MILLI2TIMESTAMPCST (I_MILLISECONDS NUMBER)
/************************************************************************************************
名称：MILLI2TIMESTAMPCST
功能：将1970-01-01 08:00:00以来的毫秒数转换为东八区(北京)timestamp时间类型，精确保留毫秒级精度！
参数：I_MILLISECONDS NUMBER 待转换的毫秒数
示例：SELECT MILLI2TIMESTAMPCST (1671072875272) FROM DUAL;
************************************************************************************************/
RETURN TIMESTAMP
AS
V_TIMESTAMPSTR TIMESTAMP(3);
BEGIN
SELECT TO_TIMESTAMP (
TO_CHAR (
TO_DATE ('1970-01-01 08:00:00',
'YYYY-MM-DD HH:MI:SS')
+ I_MILLISECONDS / (1000 * 60 * 60 * 24),
'YYYY-MM-DD HH:MI:SS')
|| '.'
|| SUBSTR (I_MILLISECONDS, -3),
'YYYY-MM-DD HH24:MI:SS.FF3') AS Trans_Timestamp
INTO V_TIMESTAMPSTR
FROM DUAL;

创建函数方法2

CREATE OR REPLACE FUNCTION MILLISECONDS2TIMESTAMPCST (I_MILLISECONDS NUMBER)
/************************************************************************************************
名称：MILLISECONDS2TIMESTAMPCST
功能：将1970-01-01 00:00:00以来的毫秒数转换为东八区(北京)timestamp时间类型，精确保留毫秒级精度！
参数：I_MILLISECONDS NUMBER 待转换的毫秒数
示例：select MILLISECONDS2TIMESTAMPCST(1671072875272) 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,
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')
|| --日期
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)
|| --小时
LPAD (
MOD (
( I_MILLISECONDS
- ( MOD (
( I_MILLISECONDS
- MOD (I_MILLISECONDS, 1000))
/ 1000,
60)
* 1000
+ MOD (I_MILLISECONDS, 1000)))
/ 1000
/ 60,
60),
2,
0)
|| --分钟
LPAD (
MOD ((I_MILLISECONDS - MOD (I_MILLISECONDS, 1000)) / 1000,
60),
2,
0)
|| --秒
LPAD (MOD (I_MILLISECONDS, 1000), 3, 0) --毫秒
INTO V_TIMESTAMPSTR
FROM DUAL;