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