日期時間運算是業務開發中極高頻的需求:計算到期日、統計近 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
| 單位關鍵字 | 含義 | 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' | 字符串格式 |
-- 當前日期時間
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;
-- 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 天」。
-- 近 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 < 結束 半開區間,避免時間組件精度問題,利用索引範圍掃描。
-- 服務器時區
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) 可用索引。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()) |
DATE_ADD(NOW(), 1 DAY) 報錯,必須 INTERVAL 1 DAY。INTERVAL -1 DAY 等同 DATE_SUB,expr 可為負數。DATE_ADD('2024-01-31', INTERVAL 1 MONTH) 返回 2024-02-29,非 2024-03-02,業務邏輯需確認是否符合預期。INTERVAL '1:30' HOUR_MINUTE,數字間隔無需引號。MySQL 日期時間運算功能強大,核心在於掌握 INTERVAL 語法、理解「月邊界自動調整」語義、構建半開區間範圍查詢利用索引、並在應用層統一時區策略。將 DATE_ADD/DATE_SUB 視為「時間導航儀」,而非簡單的加減法工具,能讓你在複雜的業務時間邏輯中保持精準與高效。