假设访问记录表名为 access_logs,其中包含字段为 time(时间戳)。
以下是查询语句:
SELECT
DATE_FORMAT(FROM_UNIXTIME(time), '%Y-%m-%d %H:00:00') AS hour,
COUNT(*) AS count
FROM
access_logs
WHERE
time >= UNIX_TIMESTAMP(CURDATE())
GROUP BY hour;
解释:
- DATE_FORMAT(FROM_UNIXTIME(time), ‘%Y-%m-%d %H:00:00’):将时间戳转换为以小时为单位的格式,例如:2022-02-09 08:00:00
- COUNT(*):统计每小时的访问次数
- WHERE time >= UNIX_TIMESTAMP(CURDATE()):只查询今天的访问记录
- GROUP BY hour:按小时归组统计数量
这样查询出来的结果会是这样的:
+---------------------+-------+
| hour | count |
+---------------------+-------+
| 2022-02-09 09:00:00 | 1234 |
| 2022-02-09 10:00:00 | 5678 |
| 2022-02-09 11:00:00 | 0 |
| 2022-02-09 12:00:00 | 1234 |
| 2022-02-09 13:00:00 | 5678 |
| 2022-02-09 14:00:00 | 0 |
| 2022-02-09 15:00:00 | 1234 |
| 2022-02-09 16:00:00 | 5678 |
| 2022-02-09 17:00:00 | 0 |
| ... | ... |
+---------------------+-------+
注:如果要统计的时间范围超过一天,需要修改 WHERE 条件。