MySQL 時間維度統計查詢全攻略：年、季、月、周、日、區間與分組

MySQL 無內建 Unix 時間戳分組函數，需先 FROM_UNIXTIME() 轉為 DATETIME 再套用日期函數。本文彙整各時間維度的 **篩選**、**分組**、**區間** 查詢範本，支援 DATETIME/TIMESTAMP/DATE 與 Unix 整數欄位。

基礎轉換函數

FROM_UNIXTIME(unix_int)        -- 整數秒 → DATETIME
UNIX_TIMESTAMP(datetime)       -- DATETIME → 整數秒
DATE(datetime)                 -- 取日期部分
NOW(), CURDATE(), CURTIME()    -- 當前時間

一、年度查詢

-- 本年度資料
SELECT * FROM t WHERE YEAR(dt) = YEAR(CURDATE());

-- 年度分組統計
SELECT YEAR(dt) AS yr, COUNT(*) FROM t GROUP BY YEAR(dt);

二、季度查詢

-- 本季度資料
SELECT * FROM t WHERE QUARTER(dt) = QUARTER(CURDATE());

-- 季度分組 (年季合併鍵)
SELECT CONCAT(YEAR(dt), ' Q', QUARTER(dt)) AS yr_q, COUNT(*) 
FROM t GROUP BY YEAR(dt), QUARTER(dt);

-- 純數字鍵值 (適合排序)
SELECT YEAR(dt)*10 + QUARTER(dt) AS yrq, COUNT(*) 
FROM t GROUP BY YEAR(dt), QUARTER(dt);

三、月度查詢

-- 本月資料 (索引友善寫法)
SELECT * FROM t 
WHERE dt >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND dt < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH);

-- 月度分組
SELECT DATE_FORMAT(dt, '%Y%m') AS ym, COUNT(*) 
FROM t GROUP BY DATE_FORMAT(dt, '%Y%m');

四、周度查詢

-- 本周資料 (週一為週首)
SELECT * FROM t 
WHERE dt >= DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY)
  AND dt < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY), INTERVAL 1 WEEK);

-- 周分組 (模式 3: 週一首)
SELECT YEARWEEK(dt, 3) AS yw, COUNT(*) 
FROM t GROUP BY YEARWEEK(dt, 3);

五、日度查詢

-- 今日資料
SELECT * FROM t WHERE DATE(dt) = CURDATE();
-- 或
SELECT * FROM t WHERE TO_DAYS(dt) = TO_DAYS(NOW());

六、相對區間 (N 天/周/月內)

-- 近 7 天
SELECT * FROM t WHERE dt >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 近 30 天 (含今日)
SELECT * FROM t WHERE dt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- 近 3 月
SELECT * FROM t WHERE dt >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

七、固定區間 (如生日區間 06-03 ~ 07-08)

SELECT * FROM users 
WHERE DATE_FORMAT(birthday, '%m-%d') BETWEEN '06-03' AND '07-08';

八、多層分組範本

-- 年 → 月 雙層分組
SELECT YEAR(dt) AS yr, MONTH(dt) AS mon, COUNT(*) 
FROM t GROUP BY YEAR(dt), MONTH(dt) ORDER BY yr, mon;

-- 年 → 季 → 月 三層
SELECT YEAR(dt) AS yr, QUARTER(dt) AS qtr, MONTH(dt) AS mon, COUNT(*) 
FROM t GROUP BY YEAR(dt), QUARTER(dt), MONTH(dt);

九、Unix 時間戳欄位專用範本

-- 將 unix_ts 轉換後再套用上述所有範本
SELECT DATE_FORMAT(FROM_UNIXTIME(unix_ts), '%Y-%m') AS ym, COUNT(*)
FROM logs GROUP BY ym;

-- 近 24 小時 (Unix 數值直接運算，極快)
SELECT * FROM logs 
WHERE unix_ts >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY));

效能關鍵：索引友善查詢模式

原則：**欄位不參與函數/運算，改用常數邊界範圍查詢**，即可利用 B+ 樹索引範圍掃描。