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;

    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;
复制