MySQL-在所有间隔之间插入行日期和时间

Question

我有一个类似的mysql数据库

---------------------------------------------------------------------------
|   startdate   |   starttime  |    enddate    |    endtime   |   status  |
---------------------------------------------------------------------------
|  2020-03-04   |   04:30:00   |  2020-03-04   |   09:00:00   |  running  |
|  2020-03-04   |   11:30:00   |  2020-03-04   |   19:30:00   |  running  |
|  2020-03-05   |   05:00:00   |  2020-03-05   |   11:15:00   |  running  |
|  2020-03-05   |   12:30:00   |  2020-03-05   |   22:08:00   |  running  |
---------------------------------------------------------------------------

我想知道如果可能的话，创建一个php(或类似的东西(脚本来插入日期/时间之间的所有间隔，并创建一个状态为"的行；停止"；。

示例：

---------------------------------------------------------------------------
|   startdate   |   starttime  |    enddate    |    endtime   |   status  |
---------------------------------------------------------------------------
|  2020-03-04   |   00:00:00   |  2020-03-04   |   04:30:00   |  stopped  | *created by this script
|  2020-03-04   |   04:30:00   |  2020-03-04   |   09:00:00   |  running  |
|  2020-03-04   |   09:00:00   |  2020-03-04   |   11:30:00   |  stopped  | *
|  2020-03-04   |   11:30:00   |  2020-03-04   |   19:30:00   |  running  |
etc.
---------------------------------------------------------------------------

这可能吗？

对不起，我的英语

Answer 1

假设您使用的是MySQL 8+，则可以使用LAG((函数将当前记录的开始日期/时间与上一条记录的结束日期/时间进行比较。当存在差异时，使用这些值来创建缺失的时间跨度：

• 上一个结束日期/时间=>新开始日期/时间
• 当前开始日期/时间==>新结束日期/时间

查询：

此查询将返回丢失的记录，如果需要，可以将这些记录插入到表中。

WITH cte AS (
-- using single datetime value for simpler logic
SELECT * 
, LAG (STR_TO_DATE(CONCAT(EndDate, ' ', EndTime), '%Y-%m-%d %H:%i:%s'), 1, NULL) 
OVER (ORDER BY EndDate, EndTime) AS PrevEndDateTime
, STR_TO_DATE(CONCAT(StartDate, ' ', StartTime), '%Y-%m-%d %H:%i:%s') AS StartDateTime            
FROM   YourTable
)
SELECT CAST( DATE_FORMAT(COALESCE(PrevEndDateTime, StartDate),'%Y-%m-%d') AS DATE ) AS StartDate
, CAST( DATE_FORMAT(COALESCE(PrevEndDateTime, StartDate),'%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

测试数据：

StartDate|StartTime|EndDate|EndTime|状态：---------|：---------|------2020-03-02|01:30:00|2020-03-02*09:00:00|正在运行2020-03-04 |04:30:00|2020-03-04|09:00:00|正在运行2020-03-04 |11:30:00|2020-03-04|19:30:00|正在运行2020-03-05|05:00:00|2020-03-05 |11:15:00|运行2020-03-05|12:30:00|2020-03-05 |22:08:00|正在运行

丢失记录：

StartDate|StartTime|EndDate|EndTime|状态：---------|：---------|------2020-03-02|00:00:00|2020-03-02*01:30:00|停止2020-03-02|09:00:00|2020-03-04|04:30:00|停止2020-03-04 |09:00:00|2020-03-04|11:30:00|停止2020-03-04|19:30:00|2020-03-05|05:00:00|停止2020-03-05|11:15:00|2020-03-05 |12:30:00|已停止

演示db<gt；小提琴这里