本文介绍如何使用 MySQL 8+ 窗口函数（LAG）自动识别时间区间空隙，并生成 status = 'stopped' 的补全记录，适用于运维日志、设备状态追踪等场景。
本文介绍如何使用 mysql 8+ 窗口函数（lag）自动识别时间区间空隙，并生成 `status = 'stopped'` 的补全记录，适用于运维日志、设备状态追踪等场景。
在监控类系统中，常需将离散的“运行中（running）”时段补全为连续的时间轴——即在相邻运行时段之间的空档期，插入 status = 'stopped' 的占位记录。例如，某设备在 2020-03-04 04:30:00 启动、09:00:00 停止，下一次启动是 11:30:00，则 09:00:00–11:30:00 这一空隙应被标记为 stopped。手动补全易出错且不可扩展，而借助 MySQL 8.0+ 的窗口函数，可完全通过 SQL 自动完成。
核心思路：用 LAG 定位时间断点
关键在于比较当前记录的起始时间与上一条记录的结束时间：
- 若二者不连续（存在时间差），说明中间存在空隙；
- 该空隙的起点即为上条记录的 enddate + endtime，终点即为当前记录的 startdate + starttime；
- 将此区间拆解为标准的 startdate/starttime/enddate/endtime 四字段格式，并设 status = 'stopped'。
为简化时间运算，我们统一将 date + time 拼接为 DATETIME 类型进行比对：
WITH cte AS (
SELECT *,
LAG(STR_TO_DATE(CONCAT(enddate, ' ', endtime), '%Y-%m-%d %H:%i:%s'), 1, NULL)
OVER (ORDER BY enddate, endtime) AS prev_end_datetime,
STR_TO_DATE(CONCAT(startdate, ' ', starttime), '%Y-%m-%d %H:%i:%s') AS start_datetime
FROM your_table_name
)
SELECT
CAST(DATE_FORMAT(COALESCE(prev_end_datetime, startdate), '%Y-%m-%d') AS DATE) AS startdate,
CAST(DATE_FORMAT(COALESCE(prev_end_datetime, startdate), '%H:%i:%s') AS TIME) AS starttime,
startdate AS enddate,
starttime AS endtime,
'stopped' AS status
FROM cte
WHERE start_datetime != prev_end_datetime
OR prev_end_datetime IS NULL;✅ 说明：
- LAG(..., 1, NULL) 获取按 enddate, endtime 排序后的前一行结束时间；首行返回 NULL，触发第一条 stopped 记录（如 00:00:00 → 第一个 starttime）；
- COALESCE(prev_end_datetime, startdate) 确保首行以当天 00:00:00 为起点；
- DATE_FORMAT(..., '%Y-%m-%d') 和 '%H:%i:%s' 分别提取日期与时间部分，再经 CAST(... AS DATE/TIME) 保证字段类型严格匹配表结构。
实际应用：插入补全记录
上述查询仅返回结果集。若需真正写入数据库，只需包装为 INSERT INTO ... SELECT：
INSERT INTO your_table_name (startdate, starttime, enddate, endtime, status)
WITH cte AS (
SELECT *,
LAG(STR_TO_DATE(CONCAT(enddate, ' ', endtime), '%Y-%m-%d %H:%i:%s'), 1, NULL)
OVER (ORDER BY enddate, endtime) AS prev_end_datetime,
STR_TO_DATE(CONCAT(startdate, ' ', starttime), '%Y-%m-%d %H:%i:%s') AS start_datetime
FROM your_table_name
)
SELECT
CAST(DATE_FORMAT(COALESCE(prev_end_datetime, startdate), '%Y-%m-%d') AS DATE),
CAST(DATE_FORMAT(COALESCE(prev_end_datetime, startdate), '%H:%i:%s') AS TIME),
startdate,
starttime,
'stopped'
FROM cte
WHERE start_datetime != prev_end_datetime
OR prev_end_datetime IS NULL;⚠️ 注意事项：
- 必须使用 MySQL 8.0+：LAG() 和 CTE（WITH）为 8.0 引入的核心特性；
- 排序逻辑至关重要：ORDER BY enddate, endtime 决定了“上一条记录”的定义，务必确保其反映真实时间流；
- 避免重复插入：建议先用 SELECT 验证结果，或添加唯一约束（如 (startdate, starttime, enddate, endtime) 联合索引）防止重复；
- 跨日空隙支持良好：因全程基于 DATETIME 运算，2020-03-04 19:30:00 → 2020-03-05 05:00:00 等跨日间隙可被准确识别；
- 性能提示：对大表执行前，确保 enddate 和 endtime 字段上有复合索引（INDEX idx_time (enddate, endtime)）。
总结
该方案以声明式 SQL 替代传统 PHP/Python 脚本循环处理，兼具简洁性、可维护性与执行效率。它不依赖外部程序，可直接集成至定时任务（如 EVENT 或 cron + mysql -e），实现全自动时间轴补全。对于需要构建连续状态视图的物联网、工控、日志分析等系统，这是符合现代数据库最佳实践的优雅解法。
