| 函數 | 說明 |
|---|---|
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 型)。
-- 今日
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;
-- 最近 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);
-- 本月
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');
-- 本周
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;
SELECT * FROM orders
WHERE created_at BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();
若欄位存儲為 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());
DATE_FORMAT(created_at)),會導致索引失效。created_at >= '2024-01-01' AND created_at < '2024-02-01',利用範圍查詢走索引。date_col DATE 欄位並建索引。YEARWEEK() 會正確處理跨年週(如 2023-12-31 屬於 2024 年第 1 週),比單純用 WEEKOFYEAR() 更可靠。
掌握 DATE_SUB、DATE_FORMAT、YEARWEEK 等核心函數的組合,即可靈活應對各類業務統計需求。實際開發中,優先使用「常量邊界值 + 範圍查詢」的寫法,兼顧可讀性與查詢性能。