MySQL 提供強大的日期時間間隔運算函數族,支援從微秒到年份的任意粒度加減運算。本文系統梳理 DATE_ADD/DATE_SUB 核心語法、INTERVAL 單位完整表與實戰應用模式。
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
-- 等價關係:DATE_SUB(d, INTERVAL expr unit) = DATE_ADD(d, INTERVAL -expr unit)
| 單位關鍵字 | 表達式格式 | 範例 | 說明 |
|---|---|---|---|
| MICROSECOND | 整數 | 123456 | 微秒 |
| SECOND | 整數 | 30 | 秒 |
| MINUTE | 整數 | 45 | 分 |
| HOUR | 整數 | 12 | 時 |
| DAY | 整數 | 15 | 天 |
| WEEK | 整數 | 2 | 週 |
| MONTH | 整數 | 3 | 月 |
| QUARTER | 整數 | 1 | 季度 |
| YEAR | 整數 | 2 | 年 |
| SECOND_MICROSECOND | '秒.微秒' | '30.123456' | 複合 |
| MINUTE_MICROSECOND | '分:秒.微秒' | '45:30.123456' | 複合 |
| MINUTE_SECOND | '分:秒' | '45:30' | 複合 |
| HOUR_MICROSECOND | '時:分:秒.微秒' | '12:45:30.123456' | 複合 |
| HOUR_SECOND | '時:分:秒' | '12:45:30' | 複合 |
| HOUR_MINUTE | '時:分' | '12:45' | 複合 |
| DAY_MICROSECOND | '天 時:分:秒.微秒' | '15 12:45:30.123456' | 複合 |
| DAY_SECOND | '天 時:分:秒' | '15 12:45:30' | 複合 |
| DAY_MINUTE | '天 時:分' | '15 12:45' | 複合 |
| DAY_HOUR | '天 時' | '15 12' | 複合 |
| YEAR_MONTH | '年-月' | '2023-10' | 複合 |
SET @dt = NOW(); -- 2023-10-15 14:30:45
-- 單位加法
SELECT DATE_ADD(@dt, INTERVAL 1 DAY); -- 2023-10-16 14:30:45
SELECT DATE_ADD(@dt, INTERVAL 1 MONTH); -- 2023-11-15 14:30:45
SELECT DATE_ADD(@dt, INTERVAL 1 YEAR); -- 2024-10-15 14:30:45
-- 複合單位加法
SELECT DATE_ADD(@dt, INTERVAL '1 12:30:45' DAY_SECOND);
-- 2023-10-17 03:01:30 (+1天12時30分45秒)
-- 減法(負間隔或 DATE_SUB)
SELECT DATE_ADD(@dt, INTERVAL -1 DAY); -- 2023-10-14 14:30:45
SELECT DATE_SUB(@dt, INTERVAL 1 DAY); -- 同上
SELECT DATE_SUB('1998-01-01', INTERVAL '1 1:1:1' DAY_SECOND);
-- 1997-12-30 22:58:59
| 函數 | 返回類型 | 語義 | 範例 |
|---|---|---|---|
DATEDIFF(d1, d2) | INT (天數) | 日期部分差值 (d1 - d2) | DATEDIFF('2023-10-15', '2023-10-10') = 5 |
TIMEDIFF(t1, t2) | TIME | 時間部分差值 (t1 - t2) | TIMEDIFF('14:30:00', '12:00:00') = '02:30:00' |
TIMESTAMPDIFF(unit, dt1, dt2) | LONG (指定單位) | 日期時間差值 (dt2 - dt1) | TIMESTAMPDIFF(DAY, '2023-10-10', '2023-10-15') = 5 |
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
-- 最近 7 天完整資料
WHERE `created_at` >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND `created_at` < CURDATE()
-- 本月迄今
WHERE `created_at` >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
AND `created_at` < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
-- 上個月完整
WHERE `created_at` >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
AND `created_at` < DATE_FORMAT(CURDATE(), '%Y-%m-01')
-- 會員到期提醒(3 天內到期)
WHERE `expire_date` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
-- 訂單超時未支付(30 分鐘)
WHERE `status` = 'pending'
AND `created_at` < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
-- 明天過生日的用戶
WHERE DATE_FORMAT(`birthday`, '%m-%d') = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%m-%d')
-- 入職周年(滿 5 年倍數)
WHERE TIMESTAMPDIFF(YEAR, `hire_date`, CURDATE()) % 5 = 0
AND DATE_FORMAT(`hire_date`, '%m-%d') = DATE_FORMAT(CURDATE(), '%m-%d')
-- 按小時分桶統計
SELECT
DATE_FORMAT(`created_at`, '%Y-%m-%d %H:00:00') AS `hour_bucket`,
COUNT(*) AS `cnt`
FROM `events`
WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY `hour_bucket`
ORDER BY `hour_bucket`;
DATE_ADD('2023-01-31', INTERVAL 1 MONTH) = '2023-02-28' (非 2/31),MySQL 自動截断到該月最後一天DATE_ADD('2024-02-29', INTERVAL 1 YEAR) = '2025-02-28'NOW() 受 time_zone 影響,UTC_TIMESTAMP() 固定 UTC,跨時區系統慎用date_hour)可考慮生成列 (MySQL 5.7+) 或物化視圖預計算MySQL 日期時間運算函數設計完備,掌握 INTERVAL 複合單位與 TIMESTAMPDIFF 多單位差值計算,能將複雜的業務時間邏輯優雅地下沉至 SQL 層,減少應用層日期庫依賴與時區 Bug。