Oracle 日期時間加減運算完整速查表

Oracle 日期時間加減運算完整速查表

日期運算是日常開發高頻操作。Oracle 提供 ADD_MONTHS、數值運算（以天為單位）、INTERVAL 語法三種方式，本文整理年/月/週/日/時/分/秒各粒度的加減法標準寫法。

核心原則

• DATE 型別：精度至秒，整數 1 = 1 天
• TIMESTAMP 型別：支援小數秒，建議使用 INTERVAL 語法
• 月份運算：必須用 ADD_MONTHS 處理月末邊界（如 1/31 + 1 月 = 2/28 或 2/29）

完整加減法速查表

實戰範例（以 SYSDATE 為基準）

-- 年
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 12)  AS plus_1_year  FROM dual;
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -12) AS minus_1_year FROM dual;

-- 月
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1)   AS plus_1_month  FROM dual;
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -1)  AS minus_1_month FROM dual;

-- 週
SELECT SYSDATE, SYSDATE + 7  AS plus_1_week  FROM dual;
SELECT SYSDATE, SYSDATE - 7  AS minus_1_week FROM dual;

-- 日
SELECT SYSDATE, SYSDATE + 1  AS plus_1_day  FROM dual;
SELECT SYSDATE, SYSDATE - 1  AS minus_1_day FROM dual;

-- 時
SELECT SYSDATE, SYSDATE + 1/24       AS plus_1_hour  FROM dual;
SELECT SYSDATE, SYSDATE - 1/24       AS minus_1_hour FROM dual;

-- 分
SELECT SYSDATE, SYSDATE + 1/24/60    AS plus_1_min  FROM dual;
SELECT SYSDATE, SYSDATE - 1/24/60    AS minus_1_min FROM dual;

-- 秒
SELECT SYSDATE, SYSDATE + 1/24/60/60 AS plus_1_sec  FROM dual;
SELECT SYSDATE, SYSDATE - 1/24/60/60 AS minus_1_sec FROM dual;

INTERVAL 語法（9i+ 推薦，語意更清晰）

-- 年/月
SELECT SYSDATE + INTERVAL '1' YEAR     FROM dual;
SELECT SYSDATE - INTERVAL '1' MONTH    FROM dual;

-- 日/時/分/秒
SELECT SYSDATE + INTERVAL '7' DAY      FROM dual;
SELECT SYSDATE + INTERVAL '1' HOUR     FROM dual;
SELECT SYSDATE + INTERVAL '30' MINUTE  FROM dual;
SELECT SYSDATE + INTERVAL '45' SECOND  FROM dual;

-- 複合間隔
SELECT SYSDATE + INTERVAL '1-6' YEAR TO MONTH  FROM dual;  -- 1 年 6 月
SELECT SYSDATE + INTERVAL '3 12:30:15' DAY TO SECOND FROM dual;  -- 3 天 12:30:15

格式化輸出範例

SELECT TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD HH24:MI:SS') AS tomorrow FROM dual;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 3), 'YYYY-MM') AS three_months_later FROM dual;

常見陷阱與最佳實務

1. 月末問題：ADD_MONTHS('31-JAN-2023', 1) → 28-FEB-2023（非 31 日），業務邏輯需確認是否符合預期
2. 時區與夏令時間：跨時區應用使用 TIMESTAMP WITH TIME ZONE + INTERVAL
3. 數值精度：1/24/60/60 為循環小數，極高精度需求改用 INTERVAL 或 NUMTODSINTERVAL
4. 索引失效：WHERE dt + 1 > SYSDATE 導致函數運算，改寫為 WHERE dt > SYSDATE - 1
5. PL/SQL 中：優先使用 SYSTIMESTAMP、INTERVAL、NUMTODSINTERVAL 避免隱含轉型

進階：工作日運算（排除週末/假日）

Oracle 無內建工作日函數，需自訂函數或使用 Calendar 表：

CREATE TABLE biz_calendar (
   cal_date DATE PRIMARY KEY,
   is_workday NUMBER(1) CHECK (is_workday IN (0,1))
);

-- 取得 N 個工作日後的日期
SELECT MIN(cal_date) FROM (
   SELECT cal_date FROM biz_calendar
   WHERE cal_date > :start_date AND is_workday = 1
   ORDER BY cal_date
) WHERE ROWNUM <= :n;