article,時間欄位 add_timeINT (UNIX 時間戳)、DATETIME、TIMESTAMPTO_DAYS(date):日期轉天數(從年 0 起),同一天差值為 0DATE_SUB(date, INTERVAL n UNIT):日期減法DATE_FORMAT(date, '%Y-%m-%d'):格式化比較CURDATE() / NOW():當前日期/日期時間-- 今天
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;
-- 今天
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;
-- 今天 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 索引,百萬級毫秒級返回。
-- 字串轉 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';