MySQL：如何获取指定日期下crawled_at的每小时统计数值

核心思路是在原SQL中添加WHERE子句，筛选出crawled_at属于目标日期的记录后再进行分组统计，以下是不同数据库环境下的实现方式：

MySQL/MariaDB #

方式一：直接匹配日期 #

SELECT COUNT(*) AS total, HOUR(crawled_at)
FROM parsed_products
WHERE DATE(crawled_at) = '2024-05-20' -- 替换为目标日期
GROUP BY HOUR(crawled_at)

方式二：时间范围匹配（更严谨，适配毫秒级时间） #

SELECT COUNT(*) AS total, HOUR(crawled_at)
FROM parsed_products
WHERE crawled_at BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59.999'
GROUP BY HOUR(crawled_at)

PostgreSQL #

方式一：匹配日期 #

SELECT COUNT(*) AS total, EXTRACT(HOUR FROM crawled_at) AS hour
FROM parsed_products
WHERE DATE(crawled_at) = '2024-05-20' -- 替换为目标日期
GROUP BY EXTRACT(HOUR FROM crawled_at)

方式二：时间范围匹配（推荐，覆盖当天所有时间） #

SELECT COUNT(*) AS total, EXTRACT(HOUR FROM crawled_at) AS hour
FROM parsed_products
WHERE crawled_at >= '2024-05-20'::DATE 
  AND crawled_at < '2024-05-21'::DATE
GROUP BY EXTRACT(HOUR FROM crawled_at)

SQL Server #

方式一：转换日期后匹配 #

SELECT COUNT(*) AS total, DATEPART(HOUR, crawled_at) AS hour
FROM parsed_products
WHERE CONVERT(DATE, crawled_at) = '2024-05-20' -- 替换为目标日期
GROUP BY DATEPART(HOUR, crawled_at)

方式二：时间范围匹配 #

SELECT COUNT(*) AS total, DATEPART(HOUR, crawled_at) AS hour
FROM parsed_products
WHERE crawled_at >= '2024-05-20' 
  AND crawled_at < DATEADD(DAY, 1, '2024-05-20')
GROUP BY DATEPART(HOUR, crawled_at)

只需将示例中的'2024-05-20'替换为你需要的特定日期即可，时间范围匹配的方式能避免因时间精度问题遗漏记录，更推荐使用。

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