如何从MySQL表中按时间区间获取记录列表用于图表展示

直接用select * from records拿数据做图表会有明显问题：只要某个时间区间（小时/天/分钟）没有业务数据，图表就会出现断层，没法完整展示趋势。下面针对不同时间粒度，给出具体实现方案，适配货币市价、资产价格、增长报告等各类场景。

核心逻辑 #

先生成连续的时间区间序列（比如近30天的所有日期、最近24小时的所有整点），再通过左连接和records表关联，用COALESCE把缺失区间的数值补成0（或null，根据图表需求调整），保证时间轴连续。

按天统计（适合日度资产价格、增长报告） #

假设records表有record_time（datetime类型）、price（市价字段）：

-- 生成近30天的连续日期
WITH RECURSIVE date_range AS (
    SELECT DATE_SUB(CURDATE(), INTERVAL 29 DAY) AS date_day
    UNION ALL
    SELECT DATE_ADD(date_day, INTERVAL 1 DAY) FROM date_range
    WHERE date_day < CURDATE()
)
SELECT 
    dr.date_day,
    COALESCE(AVG(r.price), 0) AS avg_daily_price  -- 缺失日期补0，避免图表断层
FROM date_range dr
LEFT JOIN records r ON DATE(r.record_time) = dr.date_day
GROUP BY dr.date_day
ORDER BY dr.date_day;

如果需要指定时间范围，把CURDATE()换成起始/结束日期即可，比如'2024-01-01'和'2024-01-31'。

按小时统计（适合高频货币市价） #

-- 生成最近24小时的连续整点区间
WITH RECURSIVE hour_range AS (
    SELECT DATE_SUB(NOW(), INTERVAL 23 HOUR) AS hour_point
    UNION ALL
    SELECT DATE_ADD(hour_point, INTERVAL 1 HOUR) FROM hour_range
    WHERE hour_point < NOW()
)
SELECT 
    DATE_FORMAT(hr.hour_point, '%Y-%m-%d %H:00:00') AS hour_slot,
    COALESCE(MAX(r.price), 0) AS hourly_max_price
FROM hour_range hr
LEFT JOIN records r ON DATE_FORMAT(r.record_time, '%Y-%m-%d %H') = DATE_FORMAT(hr.hour_point, '%Y-%m-%d %H')
GROUP BY hour_slot
ORDER BY hour_slot;

按分钟统计（适合超高频交易数据） #

-- 生成最近60分钟的连续分钟区间
WITH RECURSIVE minute_range AS (
    SELECT DATE_SUB(NOW(), INTERVAL 59 MINUTE) AS minute_point
    UNION ALL
    SELECT DATE_ADD(minute_point, INTERVAL 1 MINUTE) FROM minute_range
    WHERE minute_point < NOW()
)
SELECT 
    DATE_FORMAT(mr.minute_point, '%Y-%m-%d %H:%i:00') AS minute_slot,
    COALESCE(SUM(r.growth_value), 0) AS minute_total_growth
FROM minute_range mr
LEFT JOIN records r ON DATE_FORMAT(r.record_time, '%Y-%m-%d %H:%i') = DATE_FORMAT(mr.minute_point, '%Y-%m-%d %H:%i')
GROUP BY minute_slot
ORDER BY minute_slot;

适配MySQL 5.x版本（不支持递归CTE） #

如果你的MySQL版本低于8.0，可以用数字辅助表生成时间序列：

1. 先创建一个数字表：

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),...,(999); -- 插入足够多的数字，覆盖需要的时间区间数量

2. 用数字表生成日期区间：

SELECT 
    DATE_ADD('2024-01-01', INTERVAL n DAY) AS date_day
FROM numbers
WHERE DATE_ADD('2024-01-01', INTERVAL n DAY) <= '2024-01-31'
ORDER BY date_day;

再把这个结果和records表左连接，逻辑和上面一致。

注意事项 #

• 聚合函数按需选择：市价类用AVG/MAX，增长报告用SUM，根据业务场景调整。
• 时间格式统一：关联时确保两边的时间格式化规则一致，避免匹配错误。
• 缺失值处理：用COALESCE把null转为0（适合折线图），或者保留null（部分图表会自动补空）。

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