Oracle日期時間函數完全指南：TO_DATE/TO_CHAR格式模型、運算、時區、性能優化

徹底掌握Oracle日期時間：格式模型、隱式轉換陷阱、運算規則、分區裁剪關鍵

日期時間是業務系統最高頻數據類型，亦是Bug高發區。本文系統整理TO_DATE/TO_CHAR格式元素對照表、日期運算、SYSDATE/SYSTIMESTAMP區別、NLS_DATE_LANGUAGE影響、BETWEEN邊界陷阱、分區裁剪寫法、性能優化原則，構建「零Bug日期代碼」檢查清單。

核心轉換函數

格式元素速查表 (常用⭐)

實戰陷阱與正確寫法

1. 隱式轉換導致索引失效

-- 錯誤：WHERE date_col = '2024-01-15'  -- 隱式TO_DATE，fmt取決於Session NLS，可能不匹配，導致全表掃描
-- 正確：
WHERE date_col = TO_DATE('2024-01-15', 'YYYY-MM-DD')
WHERE date_col = DATE '2024-01-15'  -- ANSI日期字面量，無歧義

2. BETWEEN 邊界陷阱

-- 錯誤：查詢 2024-01-15 全天
WHERE created_dt BETWEEN TO_DATE('2024-01-15','YYYY-MM-DD') 
                     AND TO_DATE('2024-01-15','YYYY-MM-DD')
-- 實際只含 2024-01-15 00:00:00 那一秒

-- 正確寫法1：半開區間 [start, end)
WHERE created_dt >= TO_DATE('2024-01-15','YYYY-MM-DD')
  AND created_dt <  TO_DATE('2024-01-16','YYYY-MM-DD')

-- 正確寫法2：TRUNC截取日期部分
WHERE TRUNC(created_dt) = TO_DATE('2024-01-15','YYYY-MM-DD')
-- 注意：TRUNC會導致索引失效，除非建立函數索引

3. NLS_DATE_LANGUAGE 導致月份/星期解析失敗

-- Session為AMERICAN，解析中文月份失敗
TO_DATE('15-一月-2024', 'DD-MON-YYYY')  -- ORA-01843

-- 解決：顯式指定語言
TO_DATE('15-一月-2024', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE')
-- 或 ALTER SESSION SET NLS_DATE_LANGUAGE = 'SIMPLIFIED CHINESE';

4. 日期運算規則

• DATE - DATE = NUMBER (天數，含小數)
• DATE + NUMBER = DATE (天數，可為小數：0.5 = 12小時)
• INTERVAL DAY TO SECOND 類型支援精確加減 (12c+推薦)。
• MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND/TRUNC(date, 'MM') 常用函數。

分區表日期欄位裁剪最佳實踐

-- 範圍分區表 sales (partition by range(sale_dt))
-- 查詢 2024年1月數據
-- ✅ 正確：分區裁剪生效
SELECT * FROM sales 
WHERE sale_dt >= TO_DATE('2024-01-01','YYYY-MM-DD')
  AND sale_dt <  TO_DATE('2024-02-01','YYYY-MM-DD');

-- ❌ 錯誤：TRUNC導致全分區掃描
SELECT * FROM sales WHERE TRUNC(sale_dt, 'MM') = TO_DATE('2024-01','YYYY-MM');

-- ✅ 正確：虛擬欄位 + 函數索引 (11g+)
ALTER TABLE sales ADD (sale_month GENERATED ALWAYS AS (TRUNC(sale_dt, 'MM')) VIRTUAL);
CREATE INDEX idx_sales_month ON sales(sale_month);
SELECT * FROM sales WHERE sale_month = TO_DATE('2024-01','YYYY-MM');

性能優化原則

1. 統一使用字面量DATE 'YYYY-MM-DD'、TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'，避免格式字串解析開銷。
2. 避免函數包裹索引欄位：WHERE TRUNC(dt) = ... → 改半開區間或虛擬欄位索引。
3. 批量處理用數組綁定：FORALL i IN 1..cnt INSERT ... VALUES (dates(i))。
4. 時區感知：跨地域系統統一存儲TIMESTAMP WITH TIME ZONE，應用層統一UTC，顯示層按用戶時區轉換。
5. 統計信息收集：日期欄位直方圖 METHOD_OPT => 'FOR COLUMNS SIZE 254 sale_dt'，優化器精準估算基數。