PL/SQL 時間間隔 INTERVAL 類型全解析：YEAR TO MONTH 與 DAY TO SECOND 精度控制實戰

PL/SQL 時間間隔 INTERVAL 類型全解析：YEAR TO MONTH 與 DAY TO SECOND 精度控制實戰

Oracle INTERVAL 數據類型專門用於存儲「兩個時間點之間的間隔」，區別于 DATE/TIMESTAMP 表示「絕對時間點」。INTERVAL 分為兩大家族：INTERVAL YEAR TO MONTH (年月間隔) 與 INTERVAL DAY TO SECOND (天秒間隔)，各自擁有精度控制機制，是處理周期性業務、SLA 計算、分區維護的利器。

一、INTERVAL YEAR TO MONTH — 年月間隔

語法

INTERVAL YEAR [(year_precision)] TO MONTH
-- year_precision: 年份位數，範圍 0-9，默認 2 (即最大 99 年)

字面量格式

INTERVAL '100' YEAR(3)        -- 100 年，需精度 3
INTERVAL '123-2' YEAR(4) TO MONTH  -- 123 年 2 月
INTERVAL '100' MONTH           -- 100 個月

二、INTERVAL DAY TO SECOND — 天秒間隔 (高精度核心)

完整語法

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
-- day_precision: 天數位數，0-9，默認 2
-- fractional_seconds_precision: 秒小數位數，0-9，默認 6 (微秒級)

字面量格式與範圍

三、實戰案例：日期運算與分區維護

1. 基礎日期加減 (自動處理月長短、閏年)

SELECT DATE '2010-01-01' + INTERVAL '21' YEAR FROM DUAL;        -- 2031-01-01
SELECT DATE '2010-01-01' + INTERVAL '123-2' YEAR(4) TO MONTH FROM DUAL; -- 2133-03-01
SELECT DATE '2010-01-01' + INTERVAL '100' MONTH FROM DUAL;       -- 2018-05-01
SELECT DATE '2010-01-01' + INTERVAL '100' DAY(2) FROM DUAL;      -- 2010-04-11

2. 複雜間隔組合

SELECT DATE '2010-01-01' + INTERVAL '1 2' DAY TO HOUR FROM DUAL;       -- 1天2小時後
SELECT DATE '2010-01-01' + INTERVAL '1 2:3' DAY TO MINUTE FROM DUAL;   -- 1天2時3分後
SELECT DATE '2010-01-01' + INTERVAL '1 2:3:4' DAY TO SECOND FROM DUAL; -- 1天2時3分4秒後
SELECT DATE '2010-01-01' + INTERVAL '1' HOUR FROM DUAL;                -- 1小時後
SELECT DATE '2010-01-01' + INTERVAL '1:2' HOUR TO MINUTE FROM DUAL;    -- 1時2分後
SELECT DATE '2010-01-01' + INTERVAL '1:2:3' HOUR TO SECOND FROM DUAL;  -- 1時2分3秒後
SELECT DATE '2010-01-01' + INTERVAL '1:2' MINUTE TO SECOND FROM DUAL;  -- 1分2秒後

3. 業務場景：SLA 違約判斷 (精確到秒)

-- 工單創建時間 created_at，要求 4 小時內響應
SELECT 
  ticket_id,
  CASE 
    WHEN (SYSTIMESTAMP - created_at) > INTERVAL '4' HOUR 
    THEN 'SLA_BREACH' 
    ELSE 'WITHIN_SLA' 
  END AS sla_status
FROM tickets
WHERE status = 'OPEN';

4. 分區維護：自動生成按月/按日分區 DDL

-- 生成未來 12 個月分區定義
SELECT 
  'PARTITION p' || TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MM'), LEVEL-1), 'YYYYMM') || 
  ' VALUES LESS THAN (DATE ''' || TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MM'), LEVEL), 'YYYY-MM-DD') || ''')' AS partition_ddl
FROM dual
CONNECT BY LEVEL <= 12;
-- 配合 INTERVAL 分區 (11g+): PARTITION BY RANGE (created_at) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

四、精度控制避坑指南

1. 默認精度陷阱： INTERVAL DAY TO SECOND 默認 DAY(2)，最大 99 天。存儲 100 天以上間隔必須聲明 DAY(3) 或更高，否則插入報 ORA-01873: the leading precision of the interval is too small。
2. 秒精度截斷： SECOND(3) 只保留毫秒，微秒被截斷 (非四捨五入)。高精度計時必須聲明 SECOND(6) 或 (9)。
3. 字面量溢出： INTERVAL '100' DAY(2) 報錯，必須 DAY(3)。字面量解析時先檢查精度再賦值。
4. 類型混用報錯： INTERVAL YEAR TO MONTH 不能直接與 INTERVAL DAY TO SECOND 相加減，需轉換為統一單位 (如 NUMTODSINTERVAL/NUMTOYMINTERVAL)。

五、常用轉換函數速查

掌握 INTERVAL 精度聲明與字面量格式，讓你在處理「相對時間」業務時，擁有如同處理「絕對時間」般精確、可讀、可維護的代碼表達力。