MySQL 相對時間查詢完全指南：昨天、一週前、一個月前、一年前

MySQL 中使用 DATE_SUB() 配合 CURDATE() 或 NOW() 可靈活查詢相對時間範圍。本文整理標準寫法、Unix 時間戳轉換、常見陷阱與效能建議。

核心函數

DATE_SUB(date, INTERVAL expr unit)
-- 返回 date 減去間隔後的日期/時間

CURDATE()        -- 當前日期 (YYYY-MM-DD)
NOW()            -- 當前日期時間 (YYYY-MM-DD HH:MM:SS)
UNIX_TIMESTAMP() -- 當前/指定時間轉 Unix 時間戳

標準查詢模板 (DATETIME/TIMESTAMP/DATE 欄位)

-- 1. 最近 1 天 (含今天)
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)

-- 2. 最近 7 天 (一週)
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)

-- 3. 最近 30 天 (一個月)
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

-- 4. 最近 365 天 (一年)
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

-- 精確到秒 (使用 NOW())
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)

Unix 時間戳欄位 (INT/BIGINT 儲存) 查詢

關鍵： DATE_SUB() 返回日期/時間值，需轉換為時間戳比較。

-- 錯誤：直接比較時間戳與日期值
WHERE create_time > DATE_SUB(CURDATE(), INTERVAL 1 DAY)  -- 型別不匹配

-- 正確：轉換為時間戳
WHERE create_time > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
WHERE create_time > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
WHERE create_time > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
WHERE create_time > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

替代寫法：DATE_ADD 負間隔

-- 等價於 DATE_SUB
WHERE create_time >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
WHERE create_time >= DATE_ADD(CURDATE(), INTERVAL -1 WEEK)

完整應用範例

-- 建表
CREATE TABLE yh_content (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    inputtime DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 查詢昨天發布的文章
SELECT * FROM yh_content 
WHERE inputtime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND inputtime < CURDATE();  -- 精確「昨天一天」，排除今天

-- 查詢上週 (週一到週日)
SELECT * FROM yh_content 
WHERE inputtime >= DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE()) + 7) DAY)
  AND inputtime < DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);

常見時間單位關鍵字

效能優化關鍵點

1. 索引覆蓋：create_time 欄位必須有索引
2. 避免函數包裹索引列：

   -- 錯誤：索引失效
   WHERE DATE(create_time) = CURDATE()
   
   -- 正確：範圍查詢保持索引
   WHERE create_time >= CURDATE() AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)

3. 分區表：大量時間序列數據考慮 PARTITION BY RANGE (TO_DAYS(create_time))
4. 預計算邊界：應用層計算好時間邊界值傳參，減少資料庫函數調用

MySQL 8.0 新函數

-- 更語義化的時間計算
WHERE create_time >= CURDATE() - INTERVAL 1 DAY
WHERE create_time >= NOW() - INTERVAL 1 WEEK