萬盛學電腦網

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

MySQL 日期範圍查詢完全指南:今天、本周、本月、上月等常用 SQL 寫法

核心函數速查表

函數說明
NOW()當前日期時間
CURDATE()當前日期
TO_DAYS(date)日期轉天數(從年份 0 開始)
DATE_SUB(date, INTERVAL expr unit)日期減法
DATE_FORMAT(date, format)日期格式化
YEARWEEK(date)年份+週數(模式 0:週日為週首)
WEEKOFYEAR(date)年份中的週數(1-53)

常用時間範圍查詢模板

假設表名為 orders,時間欄位為 created_at(DATETIME/TIMESTAMP 型)。

1. 今日 / 昨日 / 明日

-- 今日
SELECT * FROM orders WHERE TO_DAYS(created_at) = TO_DAYS(NOW());

-- 昨日
SELECT * FROM orders WHERE TO_DAYS(NOW()) - TO_DAYS(created_at) = 1;

-- 明日(較少用)
SELECT * FROM orders WHERE TO_DAYS(created_at) - TO_DAYS(NOW()) = 1;

2. 最近 N 天(含今日)

-- 最近 7 天
SELECT * FROM orders WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(created_at);

-- 最近 30 天
SELECT * FROM orders WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(created_at);

3. 本月 / 上月

-- 本月
SELECT * FROM orders 
WHERE DATE_FORMAT(created_at, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 上月
SELECT * FROM orders 
WHERE DATE_FORMAT(created_at, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m');

4. 本周 / 上周(以週日為週首)

-- 本周
SELECT * FROM orders 
WHERE YEARWEEK(DATE_FORMAT(created_at, '%Y-%m-%d')) = YEARWEEK(NOW());

-- 上周
SELECT * FROM orders 
WHERE YEARWEEK(DATE_FORMAT(created_at, '%Y-%m-%d')) = YEARWEEK(NOW()) - 1;

5. 固定區間:過去 6 個月

SELECT * FROM orders 
WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

進階技巧與避坑指南

時間戳(INT)欄位的處理

若欄位存儲為 Unix 時間戳(如 pudate INT),需先轉換:

-- 本月(時間戳版)
SELECT * FROM user 
WHERE DATE_FORMAT(FROM_UNIXTIME(pudate), '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 本周(時間戳版)
SELECT * FROM user 
WHERE YEARWEEK(FROM_UNIXTIME(pudate)) = YEARWEEK(NOW());

索引優化建議

  • 避免在 WHERE 條件中對欄位使用函數(如 DATE_FORMAT(created_at)),會導致索引失效。
  • 推薦寫法:created_at >= '2024-01-01' AND created_at < '2024-02-01',利用範圍查詢走索引。
  • 若必須用函數,可考慮建立函數索引(MySQL 8.0+)或冗餘一個 date_col DATE 欄位並建索引。

跨年周邊界問題

YEARWEEK() 會正確處理跨年週(如 2023-12-31 屬於 2024 年第 1 週),比單純用 WEEKOFYEAR() 更可靠。

總結

掌握 DATE_SUB、DATE_FORMAT、YEARWEEK 等核心函數的組合,即可靈活應對各類業務統計需求。實際開發中,優先使用「常量邊界值 + 範圍查詢」的寫法,兼顧可讀性與查詢性能。

copyright © 萬盛學電腦網 all rights reserved