萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DATE_ADD() 與 DATE_SUB() 函數完全指南:日期時間運算、間隔類型、時區陷阱與最佳實踐

日期時間運算是業務開發中極高頻的需求:計算到期日、統計近 30 天數據、構建時間範圍查詢、處理定時任務邏輯。MySQL 提供 DATE_ADD()、DATE_SUB()、ADDDATE()、SUBDATE() 及 +/- 運算符等多種方式。本文系統梳理間隔類型、語法變體、時區行為、性能差異與實戰陷阱,助你精準驾馭時間計算。

一、核心函數語法與別名

-- 標準函數
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)

-- 同義詞 (可互換)
ADDDATE(date, INTERVAL expr unit)  -- = DATE_ADD
SUBDATE(date, INTERVAL expr unit)  -- DATE_SUB

-- 運算符簡寫 (MySQL 3.23+)
date + INTERVAL expr unit
date - INTERVAL expr unit

二、間隔單位 (unit) 完整對照表

單位關鍵字含義expr 格式示例備註
MICROSECOND微秒123456
SECOND秒30
MINUTE分鐘45
HOUR小時2
DAY天10最常用
WEEK週3
MONTH月6跨月自動處理月末日期
QUARTER季度2
YEAR年1
SECOND_MICROSECOND秒.微秒'10.500000'字符串格式
MINUTE_MICROSECOND分:秒.微秒'10:30.500000'
MINUTE_SECOND分:秒'10:30'
HOUR_MICROSECOND時:分:秒.微秒'1:30:45.500000'
HOUR_SECOND時:分:秒'1:30:45'
HOUR_MINUTE時:分'1:30'
DAY_MICROSECOND天 時:分:秒.微秒'2 1:30:45.500000'
DAY_SECOND天 時:分:秒'2 1:30:45'
DAY_MINUTE天 時:分'2 1:30'
DAY_HOUR天 時'2 1'
YEAR_MONTH年-月'2024-06'字符串格式

三、實戰範例集錦

1. 基礎增減運算

-- 當前日期時間
SELECT NOW();                    -- 2024-01-15 14:30:00

-- 加 1 天
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);        -- 2024-01-16 14:30:00
SELECT NOW() + INTERVAL 1 DAY;                 -- 同樣結果

-- 加 30 分鐘
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);    -- 2024-01-15 15:00:00

-- 加 1 年
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);       -- 2025-01-15 14:30:00

-- 減 2 小時
SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR);       -- 2024-01-15 12:30:00

-- 複雜間隔:加 1 年 2 月 3 天 4 小時 5 分鐘
SELECT DATE_ADD(NOW(), INTERVAL '1-2' YEAR_MONTH) + INTERVAL '3 4:5' DAY_MINUTE;

2. 月末邊界自動處理 (關鍵特性)

-- 1 月 31 日加 1 月 → 2 月 29/28 日 (非閏年 28 日)
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- 2024-02-29 (閏年)
SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH); -- 2023-02-28

-- 1 月 30 日加 1 月 → 2 月 29/28 日
SELECT DATE_ADD('2024-01-30', INTERVAL 1 MONTH); -- 2024-02-29

-- 1 月 29 日加 1 月 → 2 月 29 日
SELECT DATE_ADD('2024-01-29', INTERVAL 1 MONTH); -- 2024-02-29

規則:目標月若無對應日期,自動調整為該月最後一天。業務含義:「同一天數」而非「固定 30 天」。

3. 構建動態時間範圍查詢 (高頻業務場景)

-- 近 30 天訂單 (包含今天)
SELECT * FROM orders 
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 29 DAY) 
  AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY);

-- 本月數據 (利用首日、下月首日)
SELECT * FROM orders 
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND created_at < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH);

-- 上月完整數據
SELECT * FROM orders 
WHERE created_at >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
  AND created_at < DATE_FORMAT(NOW(), '%Y-%m-01');

最佳實踐:使用 >= 起始 AND < 結束 半開區間,避免時間組件精度問題,利用索引範圍掃描。

4. 結合時區處理 (關鍵進階)

-- 服務器時區
SELECT @@system_time_zone, @@session.time_zone;

-- 將 UTC 時間轉為用戶時區 (如 Asia/Shanghai)
SET time_zone = '+08:00';  -- 或 'Asia/Shanghai'
SELECT CONVERT_TZ('2024-01-15 06:30:00', '+00:00', @@session.time_zone); 
-- 結果: 2024-01-15 14:30:00

-- 日期運算在 session 時區下進行
SET time_zone = 'America/New_York';
SELECT DATE_ADD('2024-01-15 14:30:00', INTERVAL 1 DAY); -- 按紐約時間推算

鐵律:應用層統一存儲 UTC,展示層按用戶時區轉換。數據庫層面盡量避免時區轉換邏輯,交由應用層處理。

四、性能與索引考量

  • 函數列不走索引:WHERE DATE_ADD(created_at, INTERVAL 1 DAY) > NOW() 無法使用 created_at 索引。
  • 重寫為列運算:WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) 可用索引。
  • 常量折疊:MySQL 優化器會在編譯階段計算常量表達式 DATE_SUB(NOW(), INTERVAL 1 DAY),不影響索引使用。
  • 大批量更新日期:分批次、低峰期、禁用索引後重建,避免長事務鎖表。

五、相關函數速查

函數用途示例
DATEDIFF(d1, d2)兩日期相差天數 (d1 - d2)DATEDIFF('2024-01-15', '2024-01-10') = 5
TIMESTAMPDIFF(unit, d1, d2)精確間隔 (支援 MICROSECOND 等)TIMESTAMPDIFF(SECOND, '2024-01-15 10:00', '2024-01-15 10:01') = 60
LAST_DAY(date)該月最後一天LAST_DAY('2024-02-15') = '2024-02-29'
DATE_FORMAT(date, fmt)格式化輸出DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i')
STR_TO_DATE(str, fmt)字符串解析為日期STR_TO_DATE('15/01/2024', '%d/%m/%Y')
EXTRACT(unit FROM date)提取日期組件EXTRACT(MONTH FROM NOW())

六、常見陷阱避坑指南

  1. INTERVAL 關鍵字不可省略:DATE_ADD(NOW(), 1 DAY) 報錯,必須 INTERVAL 1 DAY。
  2. 負數間隔:INTERVAL -1 DAY 等同 DATE_SUB,expr 可為負數。
  3. DATE_ADD('2024-01-31', INTERVAL 1 MONTH) 返回 2024-02-29,非 2024-03-02,業務邏輯需確認是否符合預期。
  4. 字符串字面量間隔需加引號:INTERVAL '1:30' HOUR_MINUTE,數字間隔無需引號。
  5. 時區導致日期邊界偏移:跨時區系統務必在應用層統一處理,DB 層保持 UTC。

結語

MySQL 日期時間運算功能強大,核心在於掌握 INTERVAL 語法、理解「月邊界自動調整」語義、構建半開區間範圍查詢利用索引、並在應用層統一時區策略。將 DATE_ADD/DATE_SUB 視為「時間導航儀」,而非簡單的加減法工具,能讓你在複雜的業務時間邏輯中保持精準與高效。

copyright © 萬盛學電腦網 all rights reserved