Oracle 日期時間差計算全攻略：天、時、分、秒、毫秒完整轉換

Oracle 日期減法運算基礎

在 Oracle 中，兩個 DATE 型別欄位相減，結果為 天數差值（包含小數部分）。這是所有時間單位轉換的基礎。例如：

END_DATE - START_DATE  -- 結果單位：天（含小數，代表一天的分數）

各時間單位轉換公式

基於「1 天 = 24 小時 = 1440 分鐘 = 86400 秒 = 86,400,000 毫秒」的換算關係：

進階應用：使用 INTERVAL 型別

Oracle 9i 以上版本支援 INTERVAL DAY TO SECOND 型別，提供更精確且語意清晰的計算方式：

-- 直接獲得 INTERVAL 型別差值
SELECT (END_DATE - START_DATE) DAY TO SECOND AS time_diff 
FROM your_table;

-- 提取具體單位
SELECT 
  EXTRACT(DAY FROM (END_DATE - START_DATE) DAY TO SECOND) AS days,
  EXTRACT(HOUR FROM (END_DATE - START_DATE) DAY TO SECOND) AS hours,
  EXTRACT(MINUTE FROM (END_DATE - START_DATE) DAY TO SECOND) AS minutes,
  EXTRACT(SECOND FROM (END_DATE - START_DATE) DAY TO SECOND) AS seconds
FROM your_table;

實戰範例

WITH sample_data AS (
  SELECT 
    TO_DATE('2024-01-15 08:30:00', 'YYYY-MM-DD HH24:MI:SS') AS start_dt,
    TO_DATE('2024-01-18 14:45:30', 'YYYY-MM-DD HH24:MI:SS') AS end_dt
  FROM dual
)
SELECT 
  ROUND(end_dt - start_dt) AS diff_days,
  ROUND((end_dt - start_dt) * 24) AS diff_hours,
  ROUND((end_dt - start_dt) * 24 * 60) AS diff_minutes,
  ROUND((end_dt - start_dt) * 24 * 60 * 60) AS diff_seconds,
  ROUND((end_dt - start_dt) * 24 * 60 * 60 * 1000) AS diff_milliseconds
FROM sample_data;

常見陷阱與最佳實務

• ROUND vs TRUNC：ROUND 四捨五入，TRUNC 直接捨去小數；視業務需求選擇
• TIMESTAMP 型別：若欄位為 TIMESTAMP，相減結果為 INTERVAL DAY TO SECOND，需用 EXTRACT 或轉換函數處理
• 跨時區計算：涉及不同時區時，建議統一轉為 UTC 再計算，避免夏令時干擾
• 效能考量：大量資料計算時，避免在 WHERE 條件中對欄位做運算，影響索引使用

總結

掌握 Oracle 日期時間差計算的核心在於理解「日期相減得天數」這一基本原則，再配合簡單的數學換算即可獲得任意時間單位。現代版本更推薦使用 INTERVAL 型別進行語意更清晰、精度更高的處理。