整理實戰中高頻的日期範圍查詢寫法，涵蓋當天、昨日、近 N 天、本月、上月、本季、上季、本年、去年、本週、上週等場景。
DATE_FORMAT(col, '%Y-%m')：格式化為年月DATE_SUB(CURDATE(), INTERVAL n DAY)：當前日期減 n 天TO_DAYS()：日期轉天數，便於減法比較YEAR(), MONTH(), WEEK(), QUARTER()：提取對應部分YEARWEEK()：年週組合，跨年週查詢必備PERIOD_DIFF()：兩個年月期間差create_time）| 場景 | SQL 範例 |
|---|---|
| 今天 | WHERE TO_DAYS(create_time) = TO_DAYS(NOW()) |
| 昨天 | WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) <= 1 |
| 近 7 天 | WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(create_time) |
| 近 30 天 | WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(create_time) |
| 本月 | WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') |
| 上月 | WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(create_time, '%Y%m')) = 1 |
| 本季度 | WHERE QUARTER(create_time) = QUARTER(NOW()) |
| 上季度 | WHERE QUARTER(create_time) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER)) |
| 本年 | WHERE YEAR(create_time) = YEAR(NOW()) |
| 去年 | WHERE YEAR(create_time) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) |
| 本週 | WHERE YEARWEEK(DATE_FORMAT(create_time, '%Y-%m-%d')) = YEARWEEK(NOW()) |
| 上週 | WHERE YEARWEEK(DATE_FORMAT(create_time, '%Y-%m-%d')) = YEARWEEK(NOW()) - 1 |
| 近 6 月 | WHERE create_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW() |
DATE(create_time)），會導致索引失效create_time >= '2023-01-01' AND create_time < '2023-02-01' 範圍查詢，利用索引
