MySQL日期时间分栏：按小时统计每日呼叫量需求

你可以通过组合date和time列生成小时级别的时间槽，以此作为分组依据来统计每小时的呼叫量。这里提供两种常用写法：

写法1：直接拼接日期与小时 #

SELECT 
  CONCAT(DATE(date), ' ', HOUR(time), ':00:00') AS hour_slot,
  COUNT(*) AS call_count
FROM call_details
WHERE date BETWEEN '$date' AND '$date1'
GROUP BY hour_slot
ORDER BY hour_slot;

写法2：用日期函数生成标准时间格式 #

SELECT 
  DATE_FORMAT(DATE_ADD(date, INTERVAL HOUR(time) HOUR), '%Y-%m-%d %H:00:00') AS hour_slot,
  COUNT(*) AS call_count
FROM call_details
WHERE date BETWEEN '$date' AND '$date1'
GROUP BY hour_slot
ORDER BY hour_slot;

说明： #

• hour_slot 会输出类似 2024-05-20 14:00:00 的格式，代表当天14点整到15点整的时间区间
• GROUP BY hour_slot 会把同一小时的呼叫数据聚合到一起
• 加上 ORDER BY hour_slot 可以让结果按时间顺序排列，方便后续生成柱状图

如果你的date列实际是datetime类型（而非单纯的date类型），可以简化成直接提取日期和小时：

SELECT 
  DATE_FORMAT(date, '%Y-%m-%d %H:00:00') AS hour_slot,
  COUNT(*) AS call_count
FROM call_details
WHERE date BETWEEN '$date' AND '$date1'
GROUP BY hour_slot
ORDER BY hour_slot;

内容的提问来源于stack exchange，提问作者user3498801