MySQL - Insert rows date and time between all intervals-CodePudding

I have a mysql database like this

---------------------------------------------------------------------------
|   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  |

---------------------------------------------------------------------------

I want know if is possible, create a php(or something like that) script to insert all intervals between date/time and create a row with status "stopped".

Example:

---------------------------------------------------------------------------
|   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.

---------------------------------------------------------------------------

Is this possible?

Sorry my english

CodePudding user response：

Assuming you're using MySQL 8 , you could use the LAG() function to compare the current record's start date/time with the previous record's end date/time. When there's a difference, use those values to create the missing time span:

Previous End Date/Time ==> New Start Date/Time
Current Start Date/Time ==> New End Date/Time

Query:

This query will return the missing records, which you can insert into your table if desired.

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

Test Data:

StartDate  | StartTime | EndDate    | EndTime  | status 
:--------- | :-------- | :--------- | :------- | :------
2020-03-02 | 01:30:00  | 2020-03-02 | 09:00:00 | running
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

Missing Records:

StartDate  | StartTime | EndDate    | EndTime  | Status 
:--------- | :-------- | :--------- | :------- | :------
2020-03-02 | 00:00:00  | 2020-03-02 | 01:30:00 | stopped
2020-03-02 | 09:00:00  | 2020-03-04 | 04:30:00 | stopped
2020-03-04 | 09:00:00  | 2020-03-04 | 11:30:00 | stopped
2020-03-04 | 19:30:00  | 2020-03-05 | 05:00:00 | stopped
2020-03-05 | 11:15:00  | 2020-03-05 | 12:30:00 | stopped

demo db<>fiddle here