本文介绍如何利用 mysql 8.0+ 窗口函数（lag）自动识别时间区间断点，并生成缺失的“stopped”时段记录，支持直接 insert 或与 php 脚本集成，无需手动计算。
本文介绍如何利用 mysql 8.0+ 窗口函数（lag）自动识别时间区间断点，并生成缺失的“stopped”时段记录，支持直接 insert 或与 php 脚本集成，无需手动计算。
在监控类、工单系统或设备运行日志等场景中，常需将离散的“running”时段补全为连续的时间线，即在相邻运行段之间自动填充“stopped”状态的空白区间。例如：某天 04:30–09:00 和 11:30–19:30 处于运行中，则 00:00–04:30、09:00–11:30、19:30–24:00 应标记为停止。本文提供基于纯 SQL 的高效解决方案，并兼容后续程序化调用。
核心思路：用 LAG 捕获前序结束时间
关键在于将每条记录的 StartDate + StartTime 转换为完整 datetime 类型，再通过 LAG() 获取上一条记录的 EndDate + EndTime。若当前起始时间 ≠ 上一条结束时间，说明存在空隙，即可构造新记录：
- 新 StartDate / StartTime = 上一条的 EndDate + EndTime（格式化为 DATE 和 TIME）
- 新 EndDate / EndTime = 当前记录的 StartDate + StartTime
✅ 注意：该逻辑默认以自然日（00:00:00–23:59:59）为边界；若需覆盖首尾（如从当日 00:00:00 开始、到次日 00:00:00 结束），可在最终结果中额外 UNION 补充首段和末段（见文末扩展说明）。
完整可执行 SQL（MySQL 8.0+）
以下 CTE 查询将返回所有待插入的“stopped”记录，可直接用于 INSERT INTO ... SELECT：
WITH cte AS (
SELECT *,
LAG(STR_TO_DATE(CONCAT(EndDate, ' ', EndTime), '%Y-%m-%d %H:%i:%s'), 1)
OVER (ORDER BY EndDate, EndTime) AS PrevEndDateTime,
STR_TO_DATE(CONCAT(StartDate, ' ', StartTime), '%Y-%m-%d %H:%i:%s') AS StartDateTime
FROM your_table_name
)
SELECT
CAST(DATE_FORMAT(COALESCE(PrevEndDateTime, CONCAT(StartDate, ' 00:00:00')), '%Y-%m-%d') AS DATE) AS StartDate,
CAST(DATE_FORMAT(COALESCE(PrevEndDateTime, CONCAT(StartDate, ' 00:00:00')), '%H:%i:%s') AS TIME) AS StartTime,
StartDate AS EndDate,
StartTime AS EndTime,
'stopped' AS Status
FROM cte
WHERE StartDateTime != PrevEndDateTime
OR PrevEndDateTime IS NULL;? 说明：
- COALESCE(PrevEndDateTime, ...) 处理第一条记录无前驱的情况，默认设为当日 00:00:00（可根据业务调整为 StartDate - INTERVAL 1 SECOND 等）；
- ORDER BY EndDate, EndTime 确保按实际结束时间排序，避免因日期错乱导致空隙误判；
- 所有类型转换（DATE/TIME）确保字段类型与目标表严格一致。
集成到 PHP 脚本（安全执行示例）
若需通过 PHP 自动化补全，推荐使用 PDO 预处理防止注入，并分步执行（先查后插）：
<?php
$pdo = new PDO("mysql:host=localhost;dbname=your_db", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 步骤1：获取待插入的 stopped 记录
$stmt = $pdo->prepare("
WITH cte AS (
SELECT *,
LAG(STR_TO_DATE(CONCAT(EndDate, ' ', EndTime), '%Y-%m-%d %H:%i:%s'))
OVER (ORDER BY EndDate, EndTime) AS PrevEndDateTime,
STR_TO_DATE(CONCAT(StartDate, ' ', StartTime), '%Y-%m-%d %H:%i:%s') AS StartDateTime
FROM your_table_name
)
SELECT
DATE_FORMAT(COALESCE(PrevEndDateTime, CONCAT(StartDate, ' 00:00:00')), '%Y-%m-%d') AS StartDate,
DATE_FORMAT(COALESCE(PrevEndDateTime, CONCAT(StartDate, ' 00:00:00')), '%H:%i:%s') AS StartTime,
StartDate AS EndDate,
StartTime AS EndTime
FROM cte
WHERE StartDateTime != PrevEndDateTime OR PrevEndDateTime IS NULL
");
$stmt->execute();
$gaps = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 步骤2：批量插入（使用事务保障一致性）
$pdo->beginTransaction();
try {
$insert = $pdo->prepare(
"INSERT INTO your_table_name (StartDate, StartTime, EndDate, EndTime, Status)
VALUES (?, ?, ?, ?, 'stopped')"
);
foreach ($gaps as $row) {
$insert->execute([$row['StartDate'], $row['StartTime'], $row['EndDate'], $row['EndTime']]);
}
$pdo->commit();
echo "✅ 成功插入 " . count($gaps) . " 条 'stopped' 记录。\n";
} catch (Exception $e) {
$pdo->rollback();
throw $e;
}
?>注意事项与最佳实践
- 版本要求：必须使用 MySQL 8.0 或更高版本（LAG 和 CTE 为必需特性）；
- 索引优化：在 (EndDate, EndTime) 和 (StartDate, StartTime) 上建立联合索引，显著提升 ORDER BY 性能；
- 时区一致性：确保数据库、PHP 及应用层使用统一时区（推荐 UTC），避免跨时区计算偏差；
- 数据校验：上线前务必在测试库中验证边界情况（如跨日区间、重叠时段、空表）；
- 增量维护：若数据高频写入，建议封装为定时任务（如每日凌晨执行），而非每次查询实时计算。
扩展：覆盖首尾全天段（可选）
如需强制从每天 00:00:00 开始、到当天 23:59:59 结束，可在主查询外包裹一层 UNION ALL，补充首段（当日 00:00:00 → 首条 running 的开始时间）和末段（最后一条 running 的结束时间 → 当日 23:59:59），此处略去代码细节，可根据实际需求灵活添加。
通过本方案，你可完全自动化地构建连续、无歧义的时间状态视图，大幅提升数据分析与报表生成的可靠性。
