[时间/函数] Oralce 函数篇

1 日期时间函数

应用场景

CASE 毫秒级时间戳 转 Date / Timestamp / 时间字符串

-- Oracle 数据库(Oracle Database 23c - 23.3.0.23.09) | SELECT * FROM v$version
SELECT
    -- 毫秒级时间戳 转成 DATE (DATE 只能精确到秒,毫秒会被丢弃)
    TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') + (1700000000123 / 86400000) as t1 -- 15-NOV-23 (注: 86400000 = 24 * 60 * 60 * 1000) | 返回类型=Date

    -- 毫秒级时间戳 转成 保留毫秒的 TIMESTAMP
    , TIMESTAMP '1970-01-01 08:00:00' + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') AS t2 -- 15-NOV-23 06.13.20.123000000 AM | 返回类型=TIMESTAMP(6) = TIMESTAMP + INTERVAL
    , TO_CHAR( 
        TIMESTAMP '1970-01-01 08:00:00' + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS.FF3' 
    ) AS t3 -- 2023-11-15 06:13:20.123 【字符串】 | 返回类型=VARCHAR2(字符串)
    , TIMESTAMP '1970-01-01 08:00:00' + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') AS t4 -- 15-NOV-23 06.13.20.123000000 AM | 返回类型=TIMESTAMP(6)
    -- 使用 TO_TIMESTAMP 转 UTC 时间 | TO_TIMESTAMP(number) 不能直接把数字时间戳转成时间,应使用基准时间加 NUMTODSINTERVAL
    , TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') as t5 -- 14-NOV-23 10.13.20.123000000 PM | 返回类型=TIMESTAMP(6)
    -- 转中国时区 TIMESTAMP
    , TO_TIMESTAMP('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') AS t6 -- 15-NOV-23 06.13.20.123000000 AM | 返回类型=TIMESTAMP(6)
    -- 更严谨的时区转换 【推荐】
    , ( FROM_TZ( TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'UTC' ) + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') 
    ) AT TIME ZONE 'Asia/Shanghai' AS t7 -- 15-NOV-23 06.13.20.123000000 AM ASIA/SHANGHAI | 返回类型=带时区的时间戳( TIMESTAMP(6) WITH TIME ZONE )

    -- 先按 UTC 构造时间,再转换为目标时区(更严谨的方式)
    , CAST( 
      ( FROM_TZ(TIMESTAMP '1970-01-01 00:00:00', 'UTC') + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') ) AT TIME ZONE 'Asia/Shanghai' AS TIMESTAMP 
    ) AS t8 -- 15-NOV-23 06.13.20.123000 AM | 返回类型=TIMESTAMP(6)
    , FROM_TZ(TIMESTAMP '1970-01-01 00:00:00', 'UTC') + NUMTODSINTERVAL(1700000000123 / 1000, 'SECOND') AS t9 -- 14-NOV-23 10.13.20.123000000 PM UTC | 返回类型=TIMESTAMP(6) WITH TIME ZONE
FROM dual; 
  • 不支持的语法 (Mysql 和 达梦8 支持)
select 
    FROM_UNIXTIME(1794880814123/1000.0) as t1     -- 2026-11-17 10:00:14.000000     【Timestamp With Local TimeZone】 
from dual

Y 推荐文献

  • SQL Fiddle : 在线数据库SQL练习(SQL Server / SQLite / PostgreSQL / MySQL / MariaDB / Oracle / Oracle PLSQL)

X 参考文献

posted @ 2026-11-18 11:51  千千寰宇  阅读(5)  评论(0)    收藏  举报