Oracle 日期時間函數大全：27 個核心函數、格式模型、時區、閏年、分頁、脫敏實戰速查

核心函數分類速查表

格式模型元素速查（TO_CHAR/TO_DATE 通用）

實戰組合拳範例

1. 本月第一天/最後一天 00:00:00

TRUNC(SYSDATE, 'MM')                    -- 本月 1 號 00:00
LAST_DAY(TRUNC(SYSDATE)) + 1 - 1/86400  -- 本月最後一天 23:59:59 (舊寫法)
TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') - 1/86400  -- 更優雅

2. 精確天數差（含時分秒）

-- 兩個 DATE 相減 = 天數 (含小數)
SELECT (SYSDATE - TO_DATE('2024-01-01','YYYY-MM-DD')) AS days_diff FROM dual;

-- 僅整天
SELECT FLOOR(SYSDATE - TO_DATE('2024-01-01','YYYY-MM-DD')) FROM dual;

3. 年齡計算（精確到年月日）

SELECT
    FLOOR(MONTHS_BETWEEN(SYSDATE, birth_dt)/12) AS years,
    MOD(FLOOR(MONTHS_BETWEEN(SYSDATE, birth_dt)), 12) AS months,
    TRUNC(SYSDATE - ADD_MONTHS(birth_dt,
        FLOOR(MONTHS_BETWEEN(SYSDATE, birth_dt)/12)*12 +
        MOD(FLOOR(MONTHS_BETWEEN(SYSDATE, birth_dt)), 12)
    )) AS days
FROM employees;

4. 閏年判斷

-- 方法 1：2 月最後一天是否 29
CASE WHEN TO_CHAR(LAST_DAY(TO_DATE('02'||:yr,'MMYYYY')), 'DD') = '29' THEN 1 ELSE 0 END

-- 方法 2：年份規則
CASE WHEN MOD(:yr, 400) = 0 OR (MOD(:yr, 4) = 0 AND MOD(:yr, 100) != 0) THEN 1 ELSE 0 END

5. 分頁時間窗口（避免隱式轉換導致索引失效）

-- 錯誤：WHERE create_dt > '2024-01-01'  -- 字符串隱式轉換
-- 正確：
WHERE create_dt >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
  AND create_dt <  TO_DATE('2024-02-01', 'YYYY-MM-DD')  -- 閉區間 [start, end)

6. 時區感知轉換（11g+ 推薦）

-- 將 UTC 時間戳轉為上海時間
SELECT CAST(FROM_TZ(CAST(utc_ts AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Shanghai' AS TIMESTAMP)
FROM events;

-- 會話時區設置
ALTER SESSION SET TIME_ZONE = 'Asia/Shanghai';

7. 脫敏：只保留年月

SELECT TO_CHAR(birth_dt, 'YYYY-MM') FROM patients;  -- 2024-05

常見陷阱與避坑

總結

日期時間處理的核心原則：**顯式格式模型、顯式時區、優先用 TIMESTAMP WITH TIME ZONE、索引友好寫法（閉區間）、閏年/夏令時邊界測試**。掌握上述 27 函數與格式元素，覆蓋 99% 業務場景。