萬盛學電腦網

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

MySQL 日期時間加減運算完全指南:DATE_ADD/DATE_SUB、INTERVAL 單位與實戰模式

MySQL 提供強大的日期時間間隔運算函數族,支援從微秒到年份的任意粒度加減運算。本文系統梳理 DATE_ADD/DATE_SUB 核心語法、INTERVAL 單位完整表與實戰應用模式。

核心函數:DATE_ADD / DATE_SUB

DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
-- 等價關係:DATE_SUB(d, INTERVAL expr unit) = DATE_ADD(d, INTERVAL -expr unit)

INTERVAL 單位完整對照表

單位關鍵字表達式格式範例說明
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 / TIMEDIFF / TIMESTAMPDIFF

函數返回類型語義範例
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

TIMESTAMPDIFF 單位支援:

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`;

邊界情況與陷阱

  1. 月份邊界:DATE_ADD('2023-01-31', INTERVAL 1 MONTH) = '2023-02-28' (非 2/31),MySQL 自動截断到該月最後一天
  2. 閏年處理:DATE_ADD('2024-02-29', INTERVAL 1 YEAR) = '2025-02-28'
  3. 時區影響:NOW() 受 time_zone 影響,UTC_TIMESTAMP() 固定 UTC,跨時區系統慎用
  4. 類型提升:DATE + INTERVAL HOUR → 自動提升為 DATETIME
  5. NULL 传播:任一參數為 NULL 結果為 NULL

效能優化建議

  • WHERE 條件中避免對欄位使用 DATE_ADD/DATE_SUB,改寫為常量側運算
  • 大表時間範圍查詢確保時間欄位有索引,並使用左閉右開區間
  • 頻繁計算的衍生時間欄位(如 date_hour)可考慮生成列 (MySQL 5.7+) 或物化視圖預計算

MySQL 日期時間運算函數設計完備,掌握 INTERVAL 複合單位與 TIMESTAMPDIFF 多單位差值計算,能將複雜的業務時間邏輯優雅地下沉至 SQL 層,減少應用層日期庫依賴與時區 Bug。

copyright © 萬盛學電腦網 all rights reserved