MySQL 按時間範圍查詢數據的完整指南：日期、時間戳與相對時間

時間維度查詢是業務開發中最高頻的場景之一。本文系統整理 MySQL 中針對 DATE/DATETIME、Unix 時間戳（BIGINT）兩類字段的精確查詢、相對時間查詢（今日、本月、上月等）及效能優化要點。

一、 日期/時間類型字段（DATE、DATETIME、TIMESTAMP）

1. 精確匹配

-- 某一天（忽略時間部分）
SELECT * FROM `orders` WHERE DATE(`created_at`) = '2024-01-15';

-- 某個月
SELECT * FROM `orders` WHERE YEAR(`created_at`) = 2024 AND MONTH(`created_at`) = 1;

-- 某年
SELECT * FROM `orders` WHERE YEAR(`created_at`) = 2024;

2. 相對時間（動態參數，適合報表/定時任務）

-- 今天
SELECT * FROM `orders` WHERE DATE(`created_at`) = CURDATE();

-- 昨天
SELECT * FROM `orders` WHERE DATE(`created_at`) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

-- 本週（週一為起始）
SELECT * FROM `orders` WHERE YEARWEEK(`created_at`, 1) = YEARWEEK(CURDATE(), 1);

-- 本月
SELECT * FROM `orders` WHERE YEAR(`created_at`) = YEAR(CURDATE()) AND MONTH(`created_at`) = MONTH(CURDATE());

-- 上月
SELECT * FROM `orders` WHERE YEAR(`created_at`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
  AND MONTH(`created_at`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH));

-- 最近 30 天（含今天）
SELECT * FROM `orders` WHERE `created_at` >= DATE_SUB(CURDATE(), INTERVAL 29 DAY);

3. 範圍查詢（推薦寫法，利用索引）

-- 某一天完整範圍 [2024-01-15 00:00:00, 2024-01-16 00:00:00)
SELECT * FROM `orders`
WHERE `created_at` >= '2024-01-15 00:00:00'
  AND `created_at` < '2024-01-16 00:00:00';

-- 本月範圍
SELECT * FROM `orders`
WHERE `created_at` >= DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00')
  AND `created_at` < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00');

核心原則：避免在索引列上使用函數（如 DATE()、MONTH()），改用 顯式範圍條件（>= 和 <），使優化器能走索引範圍掃描（type=range）。

二、 Unix 時間戳字段（BIGINT/INT 存儲秒數）

1. 轉換函數

• FROM_UNIXTIME(ts)：時間戳 → DATETIME（受 time_zone 影響）。
• UNIX_TIMESTAMP(dt)：DATETIME → 時間戳。

2. 查詢示例

-- 今天（假設 reg_time 存儲秒級時間戳）
SELECT * FROM `users`
WHERE DATE(FROM_UNIXTIME(`reg_time`)) = CURDATE();

-- 本月（推薦：轉換為時間戳範圍，避免函數操作列）
SELECT * FROM `users`
WHERE `reg_time` >= UNIX_TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00'))
  AND `reg_time` < UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00'));

-- 上月
SELECT * FROM `users`
WHERE `reg_time` >= UNIX_TIMESTAMP(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00'))
  AND `reg_time` < UNIX_TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00'));

3. 時區陷阱提示

FROM_UNIXTIME() 受會話 time_zone 影響。若應用層寫入 UTC 時間戳，但數據庫會話時區為 +08:00，查詢「今天」會產生偏差。統一規範：應用層、數據庫會話、系統時區全部使用 UTC，或在查詢前顯式設置 SET time_zone = '+00:00';。

三、 效能優化鐵律

四、 進階：分區表按時間維護

若表按 RANGE (YEAR(created_at)) 或 RANGE (TO_DAYS(created_at)) 分區，上述範圍查詢可觸發 分區剪枝，僅掃描相關分區，效能提升數量級。注意：分區鍵必須為整數列或返回整數的表達式，DATE/DATETIME 需配合 TO_DAYS()/YEAR() 使用。

五、 總結

• 日期類型：優先用 >= start AND < end 範圍查詢，利用索引。
• 時間戳類型：應用層計算好起止時間戳常量，直接比較整數列，效能最優。
• 相對時間：用 DATE_SUB(CURDATE(), INTERVAL ...) 計算邊界，再轉為範圍條件。
• 時區：全鏈路統一 UTC，消除時區轉換 Bug。