萬盛學電腦網

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

MySQL 相對時間查詢完全指南:今日、昨日、近 N 天、本月、上月的標準寫法

業務中頻繁需要「今天的訂單」、「近 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())

DATETIME/TIMESTAMP 欄位:標準相對時間查詢

今日

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;

近 7 天(含今天)

SELECT * FROM article WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(add_time);

近 30 天

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;

INT 時間戳欄位:轉換後查詢

假設 add_time 為 INT 儲存 Unix 時間戳:

今日

SELECT * FROM article 
WHERE DATE_FORMAT(FROM_UNIXTIME(add_time), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

近 N 天通用模板

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]);

近 7 天(索引友好)

SELECT * FROM article 
WHERE add_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

CURDATE() 為常數,優化器可使用 add_time 索引做範圍掃描。

常見陷阱與避坑

  • TO_DAYS 限制:不支援 1582 年前日期(格里曆改革丟失日期)
  • 兩位數年份:MySQL 將 '97-10-07' 視為 '1997-10-07',跨世紀業務需四位年份
  • 時區一致性:應用層、資料庫層、系統層時區必須一致,建議統一用 UTC 存儲,顯示層轉換
  • 邊界包含:BETWEEN 含邊界,日期範圍查詢用 >= start AND < end 避免重疊/遺漏

MySQL 8.0+ 窗口函數替代方案

複雜相對時間統計可用窗口函數:

SELECT *, 
       MAX(CASE WHEN DATE(add_time) = CURDATE() THEN 1 ELSE 0 END) OVER () AS is_today
FROM article;

掌握純 SQL 相對時間查詢,能將業務邏輯下推至資料庫層,減少應用層日期計算與時區轉換的複雜度,提升查詢效能與代碼可維護性。

copyright © 萬盛學電腦網 all rights reserved