業務中頻繁需要「今天的訂單」、「近 7 天活躍用戶」、「上個月營收」等相對時間查詢。在應用層計算日期再拼 SQL 既繁瑣又易出錯(時區、邊界)。本文提供純 SQL 寫法,直接在資料庫層完成相對時間篩選,支援 DATETIME、TIMESTAMP、INT 時間戳三種儲存格式。
| 函數 | 用途 | 示例 |
|---|---|---|
NOW() | 當前日期時間 | 2024-01-15 14:30:00 |
CURDATE() | 當前日期 | 2024-01-15 |
DATE_SUB(date, INTERVAL n UNIT) | 日期減去間隔 | DATE_SUB(CURDATE(), INTERVAL 7 DAY) |
DATE_FORMAT(date, fmt) | 格式化日期 | DATE_FORMAT(NOW(), '%Y-%m') |
TO_DAYS(date) | 日期轉天數(從 Year 0) | TO_DAYS('2024-01-15') |
PERIOD_DIFF(p1, p2) | 月份差(YYYYMM 格式) | PERIOD_DIFF(202401, 202312)=1 |
FROM_UNIXTIME(ts) | 時間戳轉日期時間 | FROM_UNIXTIME(1705312200) |
UNIX_TIMESTAMP(dt) | 日期時間轉時間戳 | UNIX_TIMESTAMP(NOW()) |
SELECT * FROM article WHERE TO_DAYS(add_time) = TO_DAYS(NOW());
或:DATE(add_time) = CURDATE()(MySQL 5.6.5+ 可用索引)
SELECT * FROM article WHERE TO_DAYS(NOW()) - TO_DAYS(add_time) = 1;
SELECT * FROM article WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(add_time);
SELECT * FROM article WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(add_time);
SELECT * FROM article WHERE DATE_FORMAT(add_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');
SELECT * FROM article WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(add_time, '%Y%m')) = 1;
假設 add_time 為 INT 儲存 Unix 時間戳:
SELECT * FROM article
WHERE DATE_FORMAT(FROM_UNIXTIME(add_time), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');
SELECT * FROM article
WHERE FROM_UNIXTIME(add_time) >= DATE_SUB(NOW(), INTERVAL 7 DAY);
使用 STR_TO_DATE 解析精確時間字串:
SELECT * FROM article
WHERE STR_TO_DATE(add_time, '%Y-%m-%d %H:%i:%s') BETWEEN '2012-06-28 08:00:00' AND '2012-06-28 09:59:59';
避免:在欄位上使用函數(DATE(add_time)、DATE_FORMAT(...)),導致索引失效、全表掃描。
推薦:將條件改寫為欄位範圍查詢,利用索引:
SELECT * FROM article
WHERE add_time >= '2024-01-15 00:00:00'
AND add_time < '2024-01-16 00:00:00';
應用層計算當天 00:00:00 與明天 00:00:00 傳入,或用預處理:
$start = (new DateTime())->setTime(0,0,0)->format('Y-m-d H:i:s');
$end = (clone $start)->modify('+1 day')->format('Y-m-d H:i:s');
$stmt = $pdo->prepare("SELECT * FROM article WHERE add_time >= ? AND add_time < ?");
$stmt->execute([$start, $end]);
SELECT * FROM article
WHERE add_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
CURDATE() 為常數,優化器可使用 add_time 索引做範圍掃描。
BETWEEN 含邊界,日期範圍查詢用 >= start AND < end 避免重疊/遺漏複雜相對時間統計可用窗口函數:
SELECT *,
MAX(CASE WHEN DATE(add_time) = CURDATE() THEN 1 ELSE 0 END) OVER () AS is_today
FROM article;
掌握純 SQL 相對時間查詢,能將業務邏輯下推至資料庫層,減少應用層日期計算與時區轉換的複雜度,提升查詢效能與代碼可維護性。