MySQL 日期運算函數速查：DATE_SUB、DATE_ADD、INTERVAL 用法與時區陷阱

MySQL 提供豐富的日期時間運算函數，DATE_SUB/DATE_ADD 配合 INTERVAL 可實現任意粒度的加減運算。本文整理常用語法、型別轉換規則、時區影響、索引友好寫法與效能建議。

一、核心函數語法

-- 日期減法
SELECT DATE_SUB('2024-03-01', INTERVAL 7 DAY);      -- 2024-02-23
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);           -- 一個月前
SELECT DATE_SUB('2024-03-01 12:00:00', INTERVAL 90 MINUTE);  -- 2024-03-01 10:30:00

-- 日期加法
SELECT DATE_ADD('2024-03-01', INTERVAL 7 DAY);      -- 2024-03-08
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);            -- 一年後

-- 簡寫運算子（同義）
SELECT '2024-03-01' - INTERVAL 7 DAY;   -- 等同 DATE_SUB
SELECT '2024-03-01' + INTERVAL 7 DAY;   -- 等同 DATE_ADD

二、INTERVAL 單位完整表

三、實戰範例

-- 近 30 天訂單
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 本月首日 00:00:00
SELECT DATE_SUB(DATE_SUB(NOW(), INTERVAL DAY(NOW())-1 DAY), INTERVAL (HOUR(NOW())) HOUR);  -- 繁瑣
-- 簡潔寫法
SELECT DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00');
-- 或
SELECT DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 1 SECOND);

-- 索引友好寫法（避免函數包裹欄位）
-- ❌ WHERE DATE_SUB(created_at, INTERVAL 7 DAY) > '2024-01-01'  -- 無法用索引
-- ✅ WHERE created_at > DATE_ADD('2024-01-01', INTERVAL 7 DAY)   -- 常數運算，可用索引

四、時區陷阱與最佳實踐

• NOW() vs UTC_TIMESTAMP()： NOW() 返回會話時區時間，UTC_TIMESTAMP() 返回 UTC。跨時區應用統一存儲 UTC，顯示時轉換。
• time_zone 會話變數影響 NOW()、DATE_SUB(NOW(),...) 結果。建議連線池初始化執行 SET time_zone = '+00:00'。
• TIMESTAMP 欄位存儲 UTC，讀取時按會話時區轉換；DATETIME 不轉換。跨時區系統建議用 DATETIME 存儲 UTC，應用層處理顯示。

五、效能建議

1. WHERE 條件中常數側做運算，欄位側保持原樣，利用索引範圍掃描。
2. 大表按時間分區（PARTITION BY RANGE (TO_DAYS(created_at))），配合分區裁剪。
3. 避免在 SELECT 列表中對大量行做複雜日期運算，改由應用層格式化。

日期運算是業務邏輯的基礎設施。統一時區、索引友好寫法、分區策略，能讓時間維度查詢既正確又高效。