Oracle 毫秒與日期時間雙向轉換：Unix Epoch 1970 基準完整範例

現代應用常以 Unix Epoch 毫秒數（自 1970-01-01 00:00:00 UTC）傳遞時間戳記，Oracle 需正確雙向轉換並處理時區。本文提供標準 SQL 轉換式、時區調整、效能考量與常見陷阱。

核心轉換邏輯

• Epoch 起點：1970-01-01 00:00:00 UTC
• 毫秒 → 秒：除以 1000
• 秒 → Oracle DATE：DATE '1970-01-01' + (seconds / 86400)（Oracle DATE 以「天」為單位，1 天 = 86400 秒）
• 時區處理：Epoch 為 UTC，轉換後若需本地時間，需加上時區差（如 UTC+8 加 8/24 天）。

標準轉換 SQL（UTC+8 環境範例）

1. 毫秒數 → 日期時間字串

-- 參數：1406538765000 (毫秒)
SELECT TO_CHAR(
           DATE '1970-01-01' 
         + (1406538765000 / 1000 / 86400) 
         + (8/24),  -- UTC+8 時區調整
           'YYYY-MM-DD HH24:MI:SS'
       ) AS local_datetime
FROM dual;
-- 結果：2014-07-28 17:12:45

2. 日期時間字串 → 毫秒數

-- 參數：'2014-07-28 17:12:45' (假設為 UTC+8 本地時間)
SELECT ROUND(
           (TO_DATE('2014-07-28 17:12:45', 'YYYY-MM-DD HH24:MI:SS') 
            - DATE '1970-01-01' 
            - (8/24))  -- 扣除 UTC+8 差值，回歸 UTC
          ) * 86400 * 1000
       AS epoch_millis
FROM dual;
-- 結果：1406538765000

3. 取得系統當前時間 (毫秒)

-- 回傳 UTC 毫秒數
SELECT ROUND(
           (SYSTIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC')
         ) * 86400 * 1000 AS utc_millis
FROM dual;

-- 或使用 DBMS_UTILITY.GET_TIME (百分秒) * 10
SELECT DBMS_UTILITY.GET_TIME * 10 AS centisec_millis FROM dual;

可重用函數封裝

CREATE OR REPLACE FUNCTION epoch_ms_to_date (p_ms IN NUMBER, p_tz_offset_hours IN NUMBER DEFAULT 8)
RETURN DATE DETERMINISTIC IS
BEGIN
    RETURN DATE '1970-01-01' + (p_ms / 1000 / 86400) + (p_tz_offset_hours / 24);
END;
/

CREATE OR REPLACE FUNCTION date_to_epoch_ms (p_dt IN DATE, p_tz_offset_hours IN NUMBER DEFAULT 8)
RETURN NUMBER DETERMINISTIC IS
BEGIN
    RETURN ROUND((p_dt - DATE '1970-01-01' - (p_tz_offset_hours / 24)) * 86400 * 1000);
END;
/

-- 使用範例
SELECT epoch_ms_to_date(1406538765000, 8) FROM dual;  -- 2014-07-28 17:12:45
SELECT date_to_epoch_ms(DATE '2014-07-28' + 17/24 + 12/1440 + 45/86400, 8) FROM dual;  -- 1406538765000

進階：TIMESTAMP WITH TIME ZONE 精確處理

-- 使用 TIMESTAMP WITH TIME ZONE 避免手動時區計算
SELECT 
    TO_CHAR(
        FROM_TZ(CAST(DATE '1970-01-01' AS TIMESTAMP), 'UTC') 
        + NUMTODSINTERVAL(1406538765000/1000, 'SECOND')
        AT TIME ZONE 'Asia/Taipei',
        'YYYY-MM-DD HH24:MI:SS TZR'
    ) AS tz_aware
FROM dual;

-- 反向
SELECT EXTRACT(DAY FROM (CAST(TO_TIMESTAMP_TZ('2014-07-28 17:12:45 Asia/Taipei', 'YYYY-MM-DD HH24:MI:SS TZR') AS DATE) - DATE '1970-01-01')) * 86400 * 1000
-- 注意：需先轉為 UTC 再計算，建議用函數封裝

常見陷阱與避坑指南

效能提示

• 大量轉換時，避免逐列呼叫 PL/SQL 函數，改用純 SQL 運算（如上述 SELECT ... FROM big_table 直接在 SELECT 列表計算）。
• 若頻繁查詢「最近 N 毫秒資料」，建議表欄位直接儲存 EPOCH_MILLIS NUMBER，建立索引，查詢 WHERE epoch_millis > :cutoff 最快。

標準化 Epoch 毫秒轉換邏輯，統一以 UTC 儲存、應用層處理顯示時區，是現代分散式系統時間一致性的基石。