如何在MySQL中按分钟生成连续的datetime序列？

当然可以实现按分钟递增的datetime序列！先给你拆解下原SQL的逻辑，再提供两种适配你需求的可行方案：

一、原SQL逻辑拆解 #

你找到的原SQL是通过数字表笛卡尔积生成连续天数：

• t0-t4是生成0-9的数字表，它们的笛卡尔积会得到0到99999的整数（t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i）
• adddate('1970-01-01', 数字)是把1970-01-01加上对应天数，所以输出是按天递增的日期

要改成按分钟生成，只需要把时间单位从天换成分钟即可。

二、方案1：纯生成连续分钟序列（不依赖现有数据） #

适合需要完全覆盖指定时间段的场景，比如确保图表横轴没有断点：

SELECT * FROM (
    SELECT DATE_ADD('2019-01-01 00:00:00', INTERVAL (t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) MINUTE) AS selected_date
    FROM 
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) v 
WHERE selected_date BETWEEN '2019-01-01 00:00:00' AND '2019-01-15 23:59:00'
ORDER BY selected_date;

关键修改点： #

1. 把adddate替换为DATE_ADD，并指定时间单位为MINUTE
2. 起始时间精确到yyyy-mm-dd hh:mm:ss，确保生成的序列从指定分钟开始
3. 数字表组合可生成100000分钟（约69天），完全覆盖15天的需求

三、方案2：结合现有数据的优化方案（适配你的思路） #

你提出的利用现有温度数据生成时间节点的思路很实用，这里优化为补全缺失分钟的版本，避免探头离线导致的时间断点：

-- 生成覆盖数据范围的连续分钟序列（MySQL 8.0+支持CTE）
WITH minute_series AS (
    SELECT DATE_ADD((SELECT MIN(DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00')) FROM raspicontroller.temperature), 
                    INTERVAL (t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) MINUTE) AS date_time
    FROM 
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
        (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
    WHERE DATE_ADD((SELECT MIN(DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00')) FROM raspicontroller.temperature), 
                   INTERVAL (t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) MINUTE) 
          <= (SELECT MAX(DATE_FORMAT(date_time, '%Y-%m-%d %H:59:00')) FROM raspicontroller.temperature)
),
-- 计算鱼缸温度的分钟平均值
fishtank_temp AS (
    SELECT 
        CONVERT((MIN(date_time) DIV 100)*100, DATETIME) AS date_time,
        AVG(temperature) AS temperature
    FROM raspicontroller.temperature
    WHERE serial LIKE '28-000898430d59'
    GROUP BY date_time DIV 100
),
-- 计算房间温度的分钟平均值
room_temp AS (
    SELECT 
        CONVERT((MIN(date_time) DIV 100)*100, DATETIME) AS date_time,
        AVG(temperature) AS temperature
    FROM raspicontroller.temperature
    WHERE serial LIKE '28-000f9843201e'
    GROUP BY date_time DIV 100
)
-- 关联连续序列与温度数据，确保时间无断点
SELECT 
    ms.date_time,
    ft.temperature AS FishTank,
    rt.temperature AS Room
FROM minute_series ms
LEFT JOIN fishtank_temp ft ON ms.date_time = ft.date_time
LEFT JOIN room_temp rt ON ms.date_time = rt.date_time
WHERE ms.date_time BETWEEN '$date_from' AND '$date_to'
ORDER BY ms.date_time;

额外优化建议： #

• 如果你的MySQL版本低于8.0，不支持CTE，可以把CTE替换为子查询
• 可用递归CTE简化序列生成（更简洁）：

WITH RECURSIVE minute_series AS (
    SELECT '$date_from' AS date_time
    UNION ALL
    SELECT DATE_ADD(date_time, INTERVAL 1 MINUTE)
    FROM minute_series
    WHERE date_time < '$date_to'
)
SELECT * FROM minute_series;

• 缺失的温度值可以用COALESCE(ft.temperature, 0)替换为默认值（比如0）

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