Mysql时间字段查询

数据库数据库、Mysql

创建时间:2025-03-23 21:05

阅读:

log_time 字段类型是：datetime

# 按小时进行查询
SELECT DATE_FORMAT(log_time, '%Y-%m-%d %H:00:00') AS hour, COUNT(*) AS count
FROM tb_tables
WHERE log_time >= "2025-03-13 00:00:00"
  AND log_time < "2025-03-14 00:00:00"
GROUP BY hour
ORDER BY hour;

# 按天进行查询
SELECT DATE(log_time) AS day, COUNT(*) AS count
FROM tb_tables
 WHERE log_time >= "2025-03-13 00:00:00"
AND log_time < "2025-03-14 00:00:00"
GROUP BY day
ORDER BY day;

# 按6小时进行分类
SELECT
    DATE(log_time) AS log_date,
    CONCAT(FLOOR(HOUR(log_time) / 6) * 6, ':00-', FLOOR(HOUR(log_time) / 6) * 6 + 5, ':59') AS time_range,
    COUNT(*) AS log_count
FROM tb_tables
GROUP BY log_date, time_range
ORDER BY log_date, time_range;

# 按4小时进行分类
SELECT
    DATE(log_time) AS log_date,
    CONCAT(FLOOR(HOUR(log_time) / 4) * 4, ':00-', FLOOR(HOUR(log_time) / 4) * 4 + 3, ':59') AS time_range,
    COUNT(*) AS log_count
FROM tb_tables
GROUP BY log_date, time_range
ORDER BY log_date, time_range;

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。