本文介绍如何使用 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 类型进行比对：
✅
说明
：
MySQL（Linux）
MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。
下载
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：
⚠️
注意事项
：
必须使用 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），实现全自动时间轴补全。对于需要构建连续状态视图的物联网、工控、日志分析等系统，这是符合现代数据库最佳实践的优雅解法。
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;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;