Oracle日期運算小技巧：用浮點數精確控制時分秒

核心洞察

Oracle DATE 型別以天為單位進行算術運算，整數=天，小數=天的分數。

• SYSDATE + 1 = 明天同一時間
• SYSDATE - 2 = 兩天前

浮點數換算公式

實測驗證

-- 2小時前
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') cur_time,
       TO_CHAR(SYSDATE - 2/24, 'YYYY-MM-DD HH24:MI:SS') time_2h_ago
FROM dual;
-- CUR_TIME: 2009-05-19 09:21:58
-- TIME_2H_AGO: 2009-05-19 07:21:58

-- 2分鐘前
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') cur_time,
       TO_CHAR(SYSDATE - 2/1440, 'YYYY-MM-DD HH24:MI:SS') time_2m_ago
FROM dual;
-- CUR_TIME: 2009-05-19 09:24:18
-- TIME_2M_AGO: 2009-05-19 09:22:18

進階：可讀性最佳實踐

直接寫分數難閱讀，建議定義常數或使用INTERVAL (9i+)：

-- 方式1: 常數 (PL/SQL包)
CREATE PACKAGE time_util AS
  ONE_HOUR   CONSTANT NUMBER := 1/24;
  ONE_MINUTE CONSTANT NUMBER := 1/1440;
  ONE_SECOND CONSTANT NUMBER := 1/86400;
END;

-- 使用
SELECT SYSDATE - 2 * time_util.ONE_HOUR FROM dual;

-- 方式2: INTERVAL 語法 (9i+，最推薦)
SELECT SYSDATE - INTERVAL '2' HOUR FROM dual;
SELECT SYSDATE - INTERVAL '30' MINUTE FROM dual;
SELECT SYSDATE + INTERVAL '10' SECOND FROM dual;
SELECT SYSDATE + INTERVAL '1 2:30:10' DAY TO SECOND FROM dual;  -- 複合間隔

避坑指南

• DATE精確到秒，TIMESTAMP精確到微秒 (9i+)
• 跨月/跨年運算Oracle自動處理，無需手工計算天數
• 時區轉換請用TIMESTAMP WITH TIME ZONE + FROM_TZ/AT TIME ZONE

結論：理解DATE = 整數天 + 小數天本質，靈活運用分數或INTERVAL，輕鬆掌握任意粒度日期運算。