MySQL 時間欄位按天/月/年/週分組統計：DATE_FORMAT 與 CONVERT 實戰技巧

MySQL 時間欄位按天/月/年/週分組統計完整指南

場景：Unix 時間戳欄位按天分組

假設表中有 time 欄位（Unix timestamp），需統計每日記錄數。

方法：CONVERT 函數（適用 SQL Server 風格）

SELECT CONVERT(CHAR(10), time, 120) AS day, COUNT(*)
FROM table_name
GROUP BY CONVERT(CHAR(10), time, 120);

• 120：ODBC 標準格式 YYYY-MM-DD HH:MI:SS
• CHAR(10)：取前 10 字元 YYYY-MM-DD，自動去除時分秒

按月/年分組：調整字元長度

• 按月：CONVERT(CHAR(7), time, 120) → YYYY-MM
• 按年：CONVERT(CHAR(4), time, 120) → YYYY

MySQL 原生推薦：DATE_FORMAT 函數

更靈活、可讀性更強，支援豐富格式化符號：

常用格式化符號速查表

實戰範例

-- 按天分組
SELECT DATE_FORMAT(FROM_UNIXTIME(time), '%Y-%m-%d') AS day, COUNT(*)
FROM table_name
GROUP BY day;

-- 按月分組
SELECT DATE_FORMAT(FROM_UNIXTIME(time), '%Y-%m') AS month, COUNT(*)
FROM table_name
GROUP BY month;

-- 按自然週分組 (年週格式: 202401)
SELECT CONCAT(DATE_FORMAT(FROM_UNIXTIME(time), '%Y'), DATE_FORMAT(FROM_UNIXTIME(time), '%u')) AS year_week, COUNT(*)
FROM table_name
GROUP BY year_week;

-- 按小時分組
SELECT DATE_FORMAT(FROM_UNIXTIME(time), '%Y-%m-%d %H') AS hour, COUNT(*)
FROM table_name
GROUP BY hour;

進階：DATETIME/TIMESTAMP 欄位直接分組

若欄位已為 DATETIME 或 TIMESTAMP 型別，無需 FROM_UNIXTIME 轉換：

SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS day, COUNT(*)
FROM orders
GROUP BY day;

效能建議

• 大數據量分組查詢，建議在時間欄位建立索引
• 避免在 WHERE 條件中對時間欄位使用函數（導致索引失效）
• 考慮使用分區表按時間範圍裁剪掃描範圍