Oracle 日期時間運算完全指南：ADD_MONTHS、間隔運算與實戰技巧

前言

Oracle 日期運算是開發中最高頻操作之一。本文系統整理年/月/周/日/時/分/秒各級別加減運算、ADD_MONTHS 特性、間隔類型應用、格式化輸出及常見陷阱。

一、核心運算方式對比

二、常用加法運算實例

1. 年/月級 - 使用 ADD_MONTHS

-- 加 1 年
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 12) FROM DUAL;

-- 加 1 月
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1) FROM DUAL;

-- 加 6 月
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 6) FROM DUAL;

2. 週/日級 - 直接加減數值

-- 加 1 週
SELECT SYSDATE, SYSDATE + 7 FROM DUAL;

-- 加 1 天
SELECT SYSDATE, SYSDATE + 1 FROM DUAL;

-- 加 3 天
SELECT SYSDATE, SYSDATE + 3 FROM DUAL;

3. 時/分/秒級 - 分數天運算

-- 加 1 小時 (1/24 天)
SELECT SYSDATE, SYSDATE + 1/24 FROM DUAL;

-- 加 30 分鐘 (30/24/60 = 1/48 天)
SELECT SYSDATE, SYSDATE + 1/48 FROM DUAL;

-- 加 1 分鐘 (1/24/60 天)
SELECT SYSDATE, SYSDATE + 1/24/60 FROM DUAL;

-- 加 1 秒 (1/24/60/60 天)
SELECT SYSDATE, SYSDATE + 1/24/60/60 FROM DUAL;

4. 格式化輸出範例

SELECT TO_CHAR(SYSDATE + 1/24, 'YYYY-MM-DD HH24:MI:SS') AS one_hour_later FROM DUAL;

三、減法運算實例

1. 年/月級

-- 減 1 年
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -12) FROM DUAL;

-- 減 1 月
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -1) FROM DUAL;

2. 週/日/時/分/秒級

-- 減 1 週
SELECT SYSDATE, SYSDATE - 7 FROM DUAL;

-- 減 1 天
SELECT SYSDATE, SYSDATE - 1 FROM DUAL;

-- 減 1 小時
SELECT SYSDATE, SYSDATE - 1/24 FROM DUAL;

-- 減 1 分鐘
SELECT SYSDATE, SYSDATE - 1/24/60 FROM DUAL;

-- 減 1 秒
SELECT SYSDATE, SYSDATE - 1/24/60/60 FROM DUAL;

四、進階應用場景

1. 業務場景：發貨日期 + 運輸時長 = 預計送達

-- 假設 ship_date 為 DATE 類型，total_times 為運輸小時數
SELECT 
    ship_date,
    total_times,
    TO_CHAR(ship_date + total_times/24, 'DD/MM/YYYY HH24:MI') AS estimated_arrival
FROM shipments;

2. 計算兩日期差（小時數）

-- sign_in_date - (ship_date + total_times/24) 得到天數差，×24 得小時
SELECT 
    TO_CHAR(ROUND((sign_in_date - (ship_date + total_times/24)) * 24)) AS delay_hours
FROM shipments;

3. 日期截斷（去除時分秒）

-- TRUNC 截斷到天
SELECT TRUNC(SYSDATE) FROM DUAL;  -- 返回當天 00:00:00

-- TRUNC 截斷到月
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;  -- 返回本月 1 號 00:00:00

-- TRUNC 截斷到年
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;

4. 獲取日期組件

-- EXTRACT (推薦，語義清晰)
SELECT 
    EXTRACT(YEAR FROM SYSDATE) AS yr,
    EXTRACT(MONTH FROM SYSDATE) AS mon,
    EXTRACT(DAY FROM SYSDATE) AS day,
    EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) AS hr,
    EXTRACT(MINUTE FROM CAST(SYSDATE AS TIMESTAMP)) AS mi
FROM DUAL;

-- TO_CHAR (格式化靈活)
SELECT 
    TO_CHAR(SYSDATE, 'YYYY') AS yr,
    TO_CHAR(SYSDATE, 'MM') AS mon,
    TO_CHAR(SYSDATE, 'DD') AS day,
    TO_CHAR(SYSDATE, 'DAY') AS weekday,
    TO_CHAR(SYSDATE, 'HH24') AS hr
FROM DUAL;

5. 字符串轉日期

-- 標準格式
TO_DATE('2024-01-15', 'YYYY-MM-DD')

-- 含時間
TO_DATE('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')

-- 不區分大小寫
TO_DATE('2024-01-15', 'yyyy-mm-dd')

6. 判斷星期幾

SELECT TO_CHAR(TO_DATE('2024-01-15', 'YYYY-MM-DD'), 'DAY') FROM DUAL;  -- MONDAY
SELECT TO_CHAR(TO_DATE('2024-01-15', 'YYYY-MM-DD'), 'DY') FROM DUAL;    -- MON

五、INTERVAL 類型運算（10g+ 推薦）

1. 年月間隔

SELECT SYSDATE + INTERVAL '1' YEAR FROM DUAL;
SELECT SYSDATE + INTERVAL '3' MONTH FROM DUAL;
SELECT SYSDATE + INTERVAL '1-6' YEAR TO MONTH FROM DUAL;  -- 1 年 6 月

2. 天秒間隔

SELECT SYSDATE + INTERVAL '7' DAY FROM DUAL;
SELECT SYSDATE + INTERVAL '12' HOUR FROM DUAL;
SELECT SYSDATE + INTERVAL '30' MINUTE FROM DUAL;
SELECT SYSDATE + INTERVAL '45' SECOND FROM DUAL;
SELECT SYSDATE + INTERVAL '1 12:30:00' DAY TO SECOND FROM DUAL;  -- 1 天 12 時 30 分

3. 間隔運算優勢

• 語義明確，代碼可讀性強
• 自動處理閏年、閏月、夏令時等邊界
• 返回 `TIMESTAMP WITH TIME ZONE` 保留時區信息
• 支持間隔相加減：`INTERVAL '1' DAY + INTERVAL '2' HOUR`

六、常見陷阱與最佳實踐

七、性能優化建議

• 索引列上避免函數運算：`WHERE TRUNC(dt) = TRUNC(SYSDATE)` → `WHERE dt >= TRUNC(SYSDATE) AND dt < TRUNC(SYSDATE)+1`
• 分區裁剪：分區鍵為日期時，使用字面量而非函數
• 統計信息收集：日期列收集直方圖應對數據傾斜

結語

Oracle 日期運算看似簡單，但邊界處理、類型轉換、性能影響等細節決定代碼質量。建議：優先使用 ADD_MONTHS 處理年月、INTERVAL 處理精確時間、TRUNC 截斷比較、索引列拒絕函數運算，建立團隊統一日期處理規範。