如何在MySQL中生成两个时间戳间每分钟一行的时间序列

方法一：使用递归CTE（MySQL 8.0+ 支持） #

递归CTE可直接生成从起始时间到结束时间的所有分钟序列，即使中间分钟无业务数据也能保留。

基础生成连续分钟序列 #

WITH RECURSIVE minute_sequence AS (
    -- 起始时间
    SELECT STR_TO_DATE('2022-10-01 20:00:00', '%Y-%m-%d %H:%i:%s') AS minute_time
    UNION ALL
    -- 递归生成下一分钟，直到达到结束时间
    SELECT DATE_ADD(minute_time, INTERVAL 1 MINUTE)
    FROM minute_sequence
    WHERE minute_time < STR_TO_DATE('2022-10-01 21:00:00', '%Y-%m-%d %H:%i:%s')
)
-- 输出所有分钟
SELECT minute_time FROM minute_sequence ORDER BY minute_time;

结合业务数据做分钟聚合统计 #

如果需要对业务数据按分钟统计（比如统计每分钟订单数），可通过左连接业务表实现，确保无数据的分钟显示0：

WITH RECURSIVE minute_sequence AS (
    SELECT STR_TO_DATE('2022-10-01 20:00:00', '%Y-%m-%d %H:%i:%s') AS minute_time
    UNION ALL
    SELECT DATE_ADD(minute_time, INTERVAL 1 MINUTE)
    FROM minute_sequence
    WHERE minute_time < STR_TO_DATE('2022-10-01 21:00:00', '%Y-%m-%d %H:%i:%s')
)
SELECT 
    ms.minute_time,
    -- 统计对应分钟的业务记录数，无数据则为0
    COUNT(o.id) AS record_count
FROM minute_sequence ms
-- 左连接业务表，按分钟匹配
LEFT JOIN your_business_table o 
    ON DATE_FORMAT(o.timestamp_column, '%Y-%m-%d %H:%i:00') = ms.minute_time
GROUP BY ms.minute_time
ORDER BY ms.minute_time;

方法二：使用数字辅助表（兼容MySQL 5.x） #

若你的MySQL版本不支持递归CTE，可预先创建一个数字辅助表，通过计算生成连续分钟：

1. 创建数字辅助表 #

-- 创建临时数字表，包含0到足够大的数字（比如1440，覆盖一天的所有分钟）
CREATE TEMPORARY TABLE numbers (n INT);
INSERT INTO numbers VALUES 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
-- 按需插入更多数字，直到覆盖你的最大时间跨度分钟数
(11),(12),...,(60); -- 示例覆盖60分钟

2. 生成连续分钟并聚合 #

SELECT 
    DATE_ADD('2022-10-01 20:00:00', INTERVAL n MINUTE) AS minute_time,
    COUNT(o.id) AS record_count
FROM numbers
LEFT JOIN your_business_table o 
    ON DATE_FORMAT(o.timestamp_column, '%Y-%m-%d %H:%i:00') = DATE_ADD('2022-10-01 20:00:00', INTERVAL n MINUTE)
WHERE DATE_ADD('2022-10-01 20:00:00', INTERVAL n MINUTE) <= '2022-10-01 21:00:00'
GROUP BY minute_time
ORDER BY minute_time;

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