Oracle 時間區間型別（INTERVAL）與高精度 Timestamp 完整應用指南

一、為什麼需要 INTERVAL 型別？

舊版 Oracle 僅有 DATE（秒精度）與 NUMBER 儲存秒數，計算「1 個月後」、「2 年 3 個月」極不直觀。SQL:1999 標準引入 INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND，配合 TIMESTAMP 系列（含時區、分秒小數），解決：

• 日曆運算（月份長短、閏年）自動處理。
• 亞秒精度（微秒、納秒）支援高頻交易、科學計算。
• 時區感知：TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE 自動轉換。

二、核心型別語法與範例

三、關鍵轉換函數

-- 字串 → DATE
TO_DATE('2024-03-15', 'YYYY-MM-DD')
TO_DATE('2024-03-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')

-- 字串 → TIMESTAMP
TO_TIMESTAMP('2024-03-15 14:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')

-- 字串 → INTERVAL
TO_YMINTERVAL('02-06')           -- 2 年 6 月
TO_DSINTERVAL('3 12:30:00.123')  -- 3 天 12:30:00.123

-- 日期/時間 → 字串
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR')

-- 當前高精度時間
CURRENT_TIMESTAMP(6)     -- Session 時區
LOCALTIMESTAMP(6)        -- DB 時區
SYSTIMESTAMP(6)          -- 伺服器作業系統時間

四、實戰運算範例

-- 1. 取得「14 個月後」的上限日期
SELECT ADD_MONTHS(MAX(med_time), 14) FROM red_meettingrecord
WHERE med_tar_id = '225' AND med_usr_id = '001387';

-- 2. 判斷當前日期是否在上限內
SELECT CASE WHEN CURRENT_DATE < (上述上限) THEN 1 ELSE 0 END AS contains FROM DUAL;

-- 3. 精確計算兩時間點差異（回傳 INTERVAL DAY TO SECOND）
SELECT (end_ts - start_ts) DAY TO SECOND FROM event_log;

-- 4. 為日期加減 INTERVAL
SELECT SYSDATE + INTERVAL '2-6' YEAR TO MONTH FROM DUAL;   -- 2 年 6 月後
SELECT SYSTIMESTAMP + INTERVAL '3 12:30' DAY TO SECOND FROM DUAL;

-- 5. 截取 Timestamp 到秒/分/時
SELECT CAST(SYSTIMESTAMP AS DATE) FROM DUAL;  -- 去除分秒小數與時區

五、效能與相容性提醒

• INTERVAL 運算在 PL/SQL、SQL 皆可用，但部分舊版驅動程式（JDBC 10.2 以前、ODP.NET 舊版）不支援直接綁定，需轉為字串或 NUMBER 處理。
• 大量資料的日期運算建議在 SQL 層完成，避免逐行 PL/SQL 迴圈。
• 統計資訊收集時，DBMS_STATS 可正確處理 INTERVAL 欄位的高/低值。