Oracle INTERVAL 資料類型深度解析：YEAR TO MONTH 與 DAY TO SECOND 完整指南

INTERVAL YEAR TO MONTH：年月間隔

用於表示「年」與「月」的時間間隔，精確度僅至月份。

語法

INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)] [TO {YEAR | MONTH}]

• precision：年或月的位數精度，範圍 0~9，預設 2。
• 格式 'years-months'，如 '123-2' 表 123 年 2 月。

範例與精度規則

間隔運算與轉換函數

-- 間隔相加
INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH = INTERVAL '6-11' YEAR TO MONTH

-- NUMTOYMINTERVAL(n, 'unit')：數值轉年月間隔
SELECT NUMTOYMINTERVAL(100, 'YEAR')  FROM DUAL;  -- +100-00
SELECT NUMTOYMINTERVAL(100, 'MONTH') FROM DUAL;  -- +08-04 (8 年 4 月)

INTERVAL DAY TO SECOND：天至秒間隔

表示精確至秒 (含小數秒) 的時間間隔。

語法

INTERVAL '{ integer | integer time_expr | time_expr }'
{ DAY | HOUR | MINUTE } [ (leading_precision) ]
| SECOND [ (leading_precision [, fractional_seconds_precision]) ]
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]

• leading_precision：前導欄位位數，0~9，預設 2。
• fractional_seconds_precision：秒小數位數，0~9，預設 6。
• time_expr 格式：HH[:MI[:SS[.n]]] 或 MI[:SS[.n]] 或 SS[.n]。

常見範例

轉換函數 NUMTODSINTERVAL

SELECT NUMTODSINTERVAL(100, 'DAY')    FROM DUAL; -- +100 00:00:00
SELECT NUMTODSINTERVAL(100, 'HOUR')   FROM DUAL; -- +04 04:00:00
SELECT NUMTODSINTERVAL(100, 'MINUTE') FROM DUAL; -- +00 01:40:00
SELECT NUMTODSINTERVAL(100, 'SECOND') FROM DUAL; -- +00 00:01:40
-- NUMTODSINTERVAL 不支援 YEAR/MONTH 單位，會報 ORA-01760

日期相減與 INTERVAL 實務應用

兩個 DATE 相減結果為「天數」(可為負數)：

SELECT TO_DATE('1999-12-12','YYYY-MM-DD') - TO_DATE('1999-12-01','YYYY-MM-DD') FROM DUAL; -- 11
SELECT TO_DATE('2999-10-12','YYYY-MM-DD') - TO_DATE('1999-12-01','YYYY-MM-DD') FROM DUAL; -- 365193

建立儲存間隔的資料表並操作

CREATE TABLE bb (
    a DATE,
    b DATE,
    c INTERVAL YEAR(9) TO MONTH
);

INSERT INTO bb VALUES (TO_DATE('1985-12-12','YYYY-MM-DD'), TO_DATE('1984-12-01','YYYY-MM-DD'), NULL);

-- 將日期差天數轉為年月間隔存入 c
UPDATE bb SET c = NUMTOYMINTERVAL(a - b, 'YEAR');
-- 結果：+376-00 (376 年 0 月)

內部儲存結構 (進階參考)

INTERVAL YEAR TO MONTH (Type=182, Len=5 bytes)

• 4 bytes：年份差 + 0x80000000 偏移量
• 1 byte：月份差 + 60 偏移量

範例：INTERVAL '5-10' YEAR TO MONTH → DUMP: Typ=182 Len=5: 80,0,0,5,46
年：0x80000005 - 0x80000000 = 5
月：0x46 - 60 = 10

INTERVAL DAY TO SECOND (Type=183, Len=11 bytes)

• 4 bytes：天數 + 0x80000000
• 1 byte：時 + 60
• 1 byte：分 + 60
• 1 byte：秒 + 60
• 4 bytes：秒小數部分 + 0x80000000

範例：INTERVAL '5 0:0:10' DAY TO SECOND → DUMP: Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0