Oracle 日期時間操作完全指南：函數、格式化、運算與效能實戰

核心日期函數速查表

日期格式化元素完整表

間隔運算 (INTERVAL) - 10g+ 推薦

-- 年月間隔
SYSDATE - INTERVAL '2' YEAR
SYSDATE - INTERVAL '6' MONTH
SYSDATE - INTERVAL '2-6' YEAR TO MONTH

-- 日時分秒間隔
SYSDATE - INTERVAL '7' DAY
SYSDATE - INTERVAL '3' HOUR
SYSDATE - INTERVAL '30' MINUTE
SYSDATE - INTERVAL '45' SECOND
SYSDATE - INTERVAL '1 2:30:45' DAY TO SECOND

-- 運算
SYSDATE + INTERVAL '1' DAY - INTERVAL '2' HOUR
INTERVAL '2' HOUR * 3  -- 6 小時

常用實戰模式

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

TRUNC(SYSDATE, 'MM')           -- 本月 1 號 00:00:00
LAST_DAY(TRUNC(SYSDATE, 'MM')) -- 本月最後一天 00:00:00
LAST_DAY(SYSDATE) + 1 - 1/86400 -- 本月最後一天 23:59:59

2. 本年第一天/最後一天

TRUNC(SYSDATE, 'YYYY')
ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1/86400

3. 週一為週首日

TRUNC(SYSDATE, 'IW')  -- ISO 週首日 (週一)
NEXT_DAY(TRUNC(SYSDATE) - 7, 'MONDAY')  -- 相容舊版

4. 年齡/年資計算

-- 年齡 (滿歲)
FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12)

-- 精確年月日
TRUNC(MONTHS_BETWEEN(SYSDATE, birth_date)/12) || '歲 ' ||
MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, birth_date)), 12) || '月'

5. 毫秒/微秒精度

-- 當前毫秒
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3')

-- 程式執行時間 (百萬分之一秒)
DECLARE
  l_start NUMBER := DBMS_UTILITY.GET_TIME;  -- 百分之一秒
BEGIN
  -- 待測程式碼
  DBMS_OUTPUT.PUT_LINE('耗時: ' || (DBMS_UTILITY.GET_TIME - l_start)/100 || ' 秒');
END;

6. 時區轉換最佳實踐

-- 正確方式：使用 TIMESTAMP WITH TIME ZONE
SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Taipei' 
FROM DUAL;

-- Session 時區影響
ALTER SESSION SET TIME_ZONE = 'Asia/Taipei';
SELECT CURRENT_TIMESTAMP FROM DUAL;  -- 顯示台灣時間

效能與最佳實踐

1. 索引欄位勿加函數：WHERE TRUNC(dt) = TRUNC(SYSDATE) 導致全表掃描 → 改用 WHERE dt >= TRUNC(SYSDATE) AND dt < TRUNC(SYSDATE)+1
2. 統一用 TIMESTAMP WITH TIME ZONE：跨時區系統避免時區混淆
3. NLS 參數明確化：TO_CHAR(d, 'fmt', 'NLS_DATE_LANGUAGE=AMERICAN') 避免 Session 干擾
4. 分區表按日期分區：大量歷史資料查詢效能大增
5. 避免隱含轉換：WHERE date_col = '2023-01-01' → WHERE date_col = DATE '2023-01-01'

常見陷阱