| 函數 | 說明 |
|---|---|
NOW() | 當前日期時間 |
CURDATE() | 當前日期 |
DATE_SUB(date, INTERVAL n UNIT) | 日期減法 |
DATE_FORMAT(date, '%Y-%m') | 格式化輸出 |
TO_DAYS(date) | 日期轉天數(從年0開始) |
YEARWEEK(date) | 年週數 |
QUARTER(date) | 季度(1-4) |
PERIOD_DIFF(p1,p2) | 月份差(YYYYMM格式) |
create_time)
SELECT * FROM t WHERE DATE(create_time) = CURDATE();
-- 或
SELECT * FROM t WHERE TO_DAYS(create_time) = TO_DAYS(NOW());
SELECT * FROM t WHERE DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
-- 或
SELECT * FROM t WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) = 1;
SELECT * FROM t WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
SELECT * FROM t WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
SELECT * FROM t WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');
SELECT * FROM t WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(create_time, '%Y%m')) = 1;
-- 或
SELECT * FROM t WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m');
SELECT * FROM t WHERE QUARTER(create_time) = QUARTER(NOW());
SELECT * FROM t WHERE QUARTER(create_time) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER));
SELECT * FROM t WHERE YEAR(create_time) = YEAR(NOW());
SELECT * FROM t WHERE YEAR(create_time) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR));
SELECT * FROM t WHERE YEARWEEK(create_time, 3) = YEARWEEK(NOW(), 3);
SELECT * FROM t WHERE YEARWEEK(create_time, 3) = YEARWEEK(NOW(), 3) - 1;
SELECT * FROM t WHERE create_time BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();
避免在列上使用函數:
DATE(create_time) = ...會導致索引失效。
WHERE DATE(create_time) = '2023-11-10'
WHERE YEAR(create_time) = 2023
WHERE create_time >= '2023-11-10 00:00:00'
AND create_time < '2023-11-11 00:00:00'
WHERE create_time >= '2023-01-01'
AND create_time < '2024-01-01'
-- 取某日期 00:00:00
DATE(date_col)
-- 取某日期 23:59:59
DATE_ADD(DATE(date_col), INTERVAL 1 DAY) - INTERVAL 1 SECOND
-- 或
DATE_ADD(DATE(date_col), INTERVAL 86399 SECOND)
應用層組裝 WHERE create_time >= ? AND create_time < ?,傳入起始/結束時間參數,兼顧索引與可讀性。