掌握 Oracle 9i+ 間隔資料型別：INTERVAL YEAR TO MONTH 與 DAY TO SECOND

背景：9i 前的時間間隔處理痛點

Oracle 9i 以前無內建間隔型別，只能用 DATE 記錄時間點，間隔需轉換為秒數存入 NUMBER 欄位。但 60 秒=1 分、60 分=1 時、24 時=1 天，在十進位系統中換算極不直觀，計算錯誤率高。

9i 引入的 SQL:1999 標準間隔型別

搭配 TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE，可精確至亞秒並處理時區。

語法規範

INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

字面值表示法

• Year-Month：年月以連字號分隔 → INTERVAL '2-6' YEAR TO MONTH（2 年 6 月）
• Day-Second：天與時間以空格分隔，時間以冒號分隔 → INTERVAL '3 12:30:06.7' DAY TO SECOND(1)（3 天 12 時 30 分 6.7 秒）

間隔可正可負，可與 TIMESTAMP 型別加減運算，間隔之間亦可加減。

實戰範例：實驗記錄表

CREATE TABLE experiment (
  experiment_id NUMBER(9),
  experiment_desc VARCHAR2(80),
  experiment_start TIMESTAMP,
  experiment_duration INTERVAL DAY(1) TO SECOND(4)
);

INSERT INTO experiment VALUES (
  1, 'Busted urban myth',
  TIMESTAMP '2006-06-01 14:00:00',
  INTERVAL '1 02:31:15.1250' DAY(1) TO SECOND(4)
);

-- 計算結束時間
SELECT experiment_id, experiment_start,
  experiment_start + experiment_duration AS experiment_end
FROM experiment;
-- 結果：2006-06-02 16:31:15.125000000

間隔值的格式化輸出：EXTRACT 函數

TO_CHAR 無直接支援間隔型別格式模型，需用 EXTRACT 逐欄位取值組合：

SELECT EXTRACT(DAY FROM experiment_duration) || ' days, ' ||
       EXTRACT(HOUR FROM experiment_duration) || ' hours, ' ||
       EXTRACT(MINUTE FROM experiment_duration) || ' minutes' AS Duration
FROM experiment;
-- 結果：1 days, 2 hours, 31 minutes

應用優勢

• 語義清晰：型別即文件，避免「秒數」魔術數字。
• 運算直觀：start + duration = end、end - start = duration。
• 內建彙總函數支援：SUM(duration)、AVG(duration) 直接運算，無需手動換算。
• 時區感知：搭配 TIMESTAMP WITH TIME ZONE 跨時區計算自動正確。

結語

善用 INTERVAL 型別能讓時間間隔邏輯更清晰、程式碼更易維護、計算更準確，是 Oracle 9i 以降時間相關應用開發的最佳實踐。