Oracle 日期時間擷取技巧：EXTRACT、TO_CHAR、SUBSTR 三種取月份/日期元件方法比較

從 DATE/TIMESTAMP 擷取年、月、日、時、分、秒等元件，Oracle 提供多種函數。本文比較 EXTRACT、TO_CHAR、SUBSTR(TO_CHAR(...)) 三種寫法的語法、效能、型別回傳差異，助您選擇最適合的方法。

三種方法語法比較

關鍵差異

實務範例

-- 1. 取得本月第一天 00:00:00 (數值運算友善)
SELECT TRUNC(SYSDATE, 'MM') FROM dual;

-- 2. 判斷某日期是否為當月
WHERE EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM SYSDATE)
  AND EXTRACT(YEAR  FROM order_date) = EXTRACT(YEAR  FROM SYSDATE);

-- 3. 依月份分組統計 (數值 GROUP BY 效能優於字串)
SELECT EXTRACT(MONTH FROM created_dt) AS mth, COUNT(*)
FROM orders
GROUP BY EXTRACT(MONTH FROM created_dt)
ORDER BY mth;

-- 4. 顯示用途：TO_CHAR 格式化輸出
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS now_str FROM dual;

-- 5. 避免 SUBSTR 寫法（除非處理非標準字串來源）
-- BAD: SUBSTR(TO_CHAR(dt,'YYYY-MM-DD'),6,2)  -- 格式一變即壞

最佳實務建議

1. 計算、比較、分組、索引鍵 → 一律用 EXTRACT，回傳 NUMBER、效能最佳、NLS 安全。
2. 顯示、報表、匯出、字串拼接 → 用 TO_CHAR 指定明確格式模型。
3. 禁止使用 SUBSTR(TO_CHAR(...)) 擷取日期元件：脆弱、效能差、不可維護。
4. TRUNC(dt, 'MM')、TRUNC(dt, 'YYYY')、LAST_DAY(dt)、ADD_MONTHS(dt, n) 等日期運算函數常比手工拼湊更簡潔。

擴展：INTERVAL 型別擷取

SELECT EXTRACT(DAY FROM (SYSTIMESTAMP - hire_date)) AS days_diff
FROM employees;
-- 也可用 NUMTODSINTERVAL / NUMTOYMINTERVAL 建構 INTERVAL 再 EXTRACT

掌握 EXTRACT 作為核心擷取工具，搭配 TO_CHAR 處理顯示，能寫出高效能、可攜帶、易維護的日期時間 SQL。