MySQL按小时统计attendancetime对应ID数量的SQL查询需求

我有一个包含id和attendancetime列的MySQL表，希望按小时统计id的数量。

示例数据表 #

期望结果 #

尝试的查询语句 #

SELECT 
    CONCAT(
        DATE(FROM_UNIXTIME(attendancetime)), ' ',
        LPAD(FLOOR(HOUR(FROM_UNIXTIME(attendancetime)) / 3) * 3, 2, '0'), ':00:00'
    ) AS TIME,
    count(id) AS COUNT
FROM operationallog
GROUP BY DATE(FROM_UNIXTIME(attendancetime)), FLOOR(HOUR(FROM_UNIXTIME(attendancetime)) / 3)
ORDER BY TIME;

你之前的查询是按3小时区间分组，不符合按单个小时统计的需求，且期望结果仅需HH:00格式的小时部分。以下是符合要求的查询语句：

SELECT
    DATE_FORMAT(FROM_UNIXTIME(attendancetime), '%H:00') AS TIME,
    COUNT(id) AS COUNT
FROM operationallog
GROUP BY DATE_FORMAT(FROM_UNIXTIME(attendancetime), '%H:00')
ORDER BY TIME;

说明： #

1. FROM_UNIXTIME(attendancetime)将Unix时间戳转换为MySQL的datetime格式
2. DATE_FORMAT(..., '%H:00')提取小时部分并格式化为HH:00形式
3. 按格式化后的小时字符串分组，统计对应小时内的id数量
4. 最终按TIME字段排序，保证结果顺序规范

如果需要区分不同日期的同一小时（比如2024-09-02 11:00和2024-09-03 11:00），可以修改为包含日期的格式：

SELECT
    DATE_FORMAT(FROM_UNIXTIME(attendancetime), '%Y-%m-%d %H:00') AS TIME,
    COUNT(id) AS COUNT
FROM operationallog
GROUP BY DATE_FORMAT(FROM_UNIXTIME(attendancetime), '%Y-%m-%d %H:00')
ORDER BY TIME;

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