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;
RETURN V_TIMESTAMPSTR;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
-- 创建函数方法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;
RETURN TO_TIMESTAMP (V_TIMESTAMPSTR, 'yyyyMMddhh24missff3') + INTERVAL '8' HOUR;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
