mysql按小时统计

SELECT 
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour,
    COUNT(*) AS count
FROM 
    logs
GROUP BY 
    hour
ORDER BY 
    hour;

SELECT 
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour,
    COUNT(*) AS count
FROM 
    logs
GROUP BY 
    hour
ORDER BY 
    hour;

-- 创建索引
CREATE INDEX idx_timestamp ON logs (timestamp);

-- 分区表示例
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME NOT NULL,
    data TEXT
)
PARTITION BY RANGE (TO_SECONDS(timestamp)) (
    PARTITION p0 VALUES LESS THAN (TO_SECONDS('2023-01-01 00:00:00')),
    PARTITION p1 VALUES LESS THAN (TO_SECONDS('2023-02-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN (TO_SECONDS('2023-03-01 00:00:00')),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);