Oracle日期時間處理完全指南：格式、運算與轉換

Oracle日期時間處理完全指南：格式、運算與轉換

日期時間處理是Oracle開發高頻需求。本文系統整理格式模型、間隔運算、字符/日期雙向轉換及常用函數，提供即查即用的參考手冊。

常用日期格式元素

關鍵點：24小時制必須用HH24，HH為12小時制。

日期間隔運算（INTERVAL關鍵字）

-- 當前時間減7分鐘
SELECT sysdate, sysdate - INTERVAL '7' MINUTE FROM dual;

-- 減7小時
SELECT sysdate - INTERVAL '7' HOUR FROM dual;

-- 減7天
SELECT sysdate - INTERVAL '7' DAY FROM dual;

-- 減7月
SELECT sysdate - INTERVAL '7' MONTH FROM dual;

-- 減7年
SELECT sysdate - INTERVAL '7' YEAR FROM dual;

-- 間隔乘法：減16小時(8×2小時)
SELECT sysdate - 8 * INTERVAL '2' HOUR FROM dual;

優勢：語義清晰，自動處理月份天數差異、閏年等邊界情況。

日期轉字符 (TO_CHAR)

SELECT sysdate, 
  TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') AS fmt1,
  TO_CHAR(sysdate, 'YYYY-MM-DD HH:MI:SS')  AS fmt2,  -- 12小時制
  TO_CHAR(sysdate, 'YYYY-DDD HH:MI:SS')    AS fmt3,  -- 年積日
  TO_CHAR(sysdate, 'YYYY-MM IW-D HH:MI:SS') AS fmt4   -- ISO周/天
FROM dual;

字符轉日期 (TO_DATE)

SELECT TO_DATE('2003-10-17 21:15:37', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

格式模型與TO_CHAR一致，需保證輸入字符串與格式模式完全匹配。

進階應用技巧

1. 截取日期部分 (TRUNC)

TRUNC(sysdate)           -- 當天零點
TRUNC(sysdate, 'MM')     -- 本月第一天零點
TRUNC(sysdate, 'YYYY')   -- 今年第一天零點
TRUNC(sysdate, 'IW')     -- 本周一零點(ISO)

2. 日期邊界計算

-- 本月最後一天
SELECT LAST_DAY(sysdate) FROM dual;

-- 下個月第一天
SELECT TRUNC(ADD_MONTHS(sysdate, 1), 'MM') FROM dual;

-- 兩日期間月數
SELECT MONTHS_BETWEEN(date1, date2) FROM dual;

3. 時區處理 (10g+)

-- 當前會話時區
SELECT SESSIONTIMEZONE FROM dual;

-- 帶時區時間戳
SELECT SYSTIMESTAMP FROM dual;

-- 時區轉換
SELECT FROM_TZ(CAST(sysdate AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Shanghai' FROM dual;

常見陷阱與最佳實踐

1. 隱式轉換風險：永遠顯式使用TO_DATE/TO_CHAR，避免依賴NLS_DATE_FORMAT
2. 格式模型不匹配：ORA-018xx錯誤多因字符串與格式不符，調試時輸出原始值核對
3. 時區數據一致性：分布式系統統一存儲UTC，顯示層按用戶時區轉換
4. 索引失效：WHERE子句對日期欄位使用函數會導致索引失效，改用範圍查詢：WHERE dt >= TRUNC(sysdate) AND dt < TRUNC(sysdate+1)

總結

掌握格式元素、INTERVAL運算、雙向轉換函數、TRUNC/LAST_DAY等邊界函數、時區處理，能解決90%以上日期時間業務場景。關鍵在於：顯式格式化、統一UTC存儲、避免隱式轉換、注意索引友好寫法。