資料庫毫秒時間戳轉日期時間：MySQL FROM_UNIXTIME 與 Oracle 自定義函數對比

開發中常將毫秒/秒級時間戳存入資料庫，查詢時需轉換為可讀日期。本文對比 MySQL 內建函數、Oracle 自定義函數、時區處理、精度遷移要點。

MySQL：FROM_UNIXTIME() (秒級) + 毫秒轉換

-- 秒級時間戳 → 日期時間
SELECT FROM_UNIXTIME(1);
-- 1970-01-01 08:00:01 (UTC+8 顯示)

-- 指定格式
SELECT FROM_UNIXTIME(1, '%Y-%m-%d %H:%i:%S');
-- 1970-01-01 08:00:01

-- 毫秒級時間戳 → 日期時間 (需除 1000)
SELECT FROM_UNIXTIME(1577836800000 / 1000, '%Y-%m-%d %H:%i:%s.%f');
-- 2020-01-01 00:00:00.000000

-- 反向：日期時間 → 秒級時間戳
SELECT UNIX_TIMESTAMP('2020-01-01 00:00:00');
-- 1577808000

-- 日期時間 → 毫秒時間戳
SELECT UNIX_TIMESTAMP('2020-01-01 00:00:00') * 1000;
-- 1577808000000

關鍵點： FROM_UNIXTIME 參數單位為**秒**，毫秒需除 1000。時區由 time_zone 系統變數決定 (預設 SYSTEM)。

Oracle：自定義函數長整型轉日期 (無內建函數)

CREATE OR REPLACE FUNCTION long2date (param IN NUMBER) RETURN VARCHAR2 AS
    v_text VARCHAR2(64);
BEGIN
    -- 1970-01-01 基準 + 毫秒轉天數 (24*60*60*1000) + 時區偏移 (8小時=8/24天) - 閏秒修正
    SELECT TO_CHAR(
        TO_DATE('1970-01-01', 'YYYY-MM-DD') + 
        (param / (24*60*60*1000)) + 
        (8/24) - (1/86400),
        'YYYY-MM-DD HH24:MI:SS'
    ) INTO v_text FROM dual;
    RETURN v_text;
END long2date;
/

-- 測試
SELECT long2date(1000) FROM dual;
-- 1970-01-01 08:00:00

-- 反向：日期 → 毫秒
SELECT (SYSDATE - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400000 FROM dual;
-- 1357571358000 (示例值)

關鍵參數解析

時區處理最佳實踐

1. 統一儲存 UTC：資料庫層儲存 UTC 時間 (TIMESTAMP 默認 UTC、DATETIME 應用層轉 UTC)
2. 應用層轉換顯示：查詢返回 UTC，前端按用戶時區格式化
3. 避免資料庫層時區運算：CONVERT_TZ()、AT TIME ZONE 影響索引使用、增加 CPU

遷移對照表

MySQL 8.0 新增：FROM_UNIXTIME 支援微秒

SELECT FROM_UNIXTIME(1577836800.123456, '%Y-%m-%d %H:%i:%s.%f');
-- 2020-01-01 00:00:00.123456