萬盛學電腦網

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

MySQL 相對時間範圍查詢大全:今天、昨天、7天、30天、本月、上月

前置假設

  • 表 article,時間欄位 add_time
  • 三種常見型別:INT (UNIX 時間戳)、DATETIME、TIMESTAMP

通用函數說明

  • TO_DAYS(date):日期轉天數(從年 0 起),同一天差值為 0
  • DATE_SUB(date, INTERVAL n UNIT):日期減法
  • DATE_FORMAT(date, '%Y-%m-%d'):格式化比較
  • CURDATE() / NOW():當前日期/日期時間

查詢語句庫

1. 時間戳 (INT) 型別

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

-- 昨天
SELECT * FROM article 
WHERE TO_DAYS(FROM_UNIXTIME(add_time)) = TO_DAYS(NOW()) - 1;

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

-- 近 30 天
SELECT * FROM article 
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(FROM_UNIXTIME(add_time));

-- 本月
SELECT * FROM article 
WHERE DATE_FORMAT(FROM_UNIXTIME(add_time), '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

-- 上月
SELECT * FROM article 
WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(FROM_UNIXTIME(add_time), '%Y%m')) = 1;

2. DATETIME / TIMESTAMP 型別(更簡潔)

-- 今天
SELECT * FROM article WHERE TO_DAYS(add_time) = TO_DAYS(NOW());

-- 昨天
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;

3. 精確到秒級範圍(推薦索引友好寫法)

-- 今天 00:00:00 ~ 23:59:59
SELECT * FROM article 
WHERE add_time >= '2024-01-15 00:00:00' 
  AND add_time <  '2024-01-16 00:00:00';

-- 應用層動態生成(PHP 範例)
$todayStart = date('Y-m-d 00:00:00');
$todayEnd   = date('Y-m-d 23:59:59');
$sql = "SELECT * FROM article WHERE add_time >= '$todayStart' AND add_time <= '$todayEnd'";

索引優化關鍵

✅ 範圍查詢走索引:add_time >= '...' AND add_time < '...'

❌ 函數包裹欄位不走索引:DATE(add_time) = ...、TO_DAYS(add_time) = ...

💡 最佳實踐:應用層計算好起止時間字串,直接用欄位範圍比較,配合 add_time 索引,百萬級毫秒級返回。

STR_TO_DATE 精確解析

-- 字串轉 DATETIME
SELECT STR_TO_DATE('2024-01-15 14:30:00', '%Y-%m-%d %H:%i:%s');

-- 查詢某小時區間
SELECT * FROM article 
WHERE STR_TO_DATE(add_time, '%Y-%m-%d %H:%i:%s') BETWEEN '2024-01-15 08:00:00' AND '2024-01-15 09:59:59';
copyright © 萬盛學電腦網 all rights reserved