Tengo una base de datos mysql como esta
--------------------------------------------------------------------------- | 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 | ---------------------------------------------------------------------------Quiero saber si es posible, crear un script php (o algo así) para insertar todos los intervalos entre la fecha/hora y crear una fila con el estado "detenido".
Ejemplo:
--------------------------------------------------------------------------- | 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. ---------------------------------------------------------------------------es posible?
Perdon por mi inglés
Suponiendo que está usando MySQL 8+, podría usar la función LAG() para comparar la fecha/hora de inicio del registro actual con la fecha/hora de finalización del registro anterior. Cuando haya una diferencia, use esos valores para crear el intervalo de tiempo que falta:
Consulta:
Esta consulta devolverá los registros faltantes, que puede insertar en su tabla si lo desea.
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 NULLDatos de prueba:
Fecha de inicio | hora de inicio | Fecha de finalización | hora de finalización | estado :--------- | :-------- | :--------- | :------- | :------ 2020-03-02 | 01:30:00 | 2020-03-02 | 09:00:00 | correr 2020-03-04 | 04:30:00 | 2020-03-04 | 09:00:00 | correr 2020-03-04 | 11:30:00 | 2020-03-04 | 19:30:00 | correr 2020-03-05 | 05:00:00 | 2020-03-05 | 11:15:00 | correr 2020-03-05 | 12:30:00 | 2020-03-05 | 22:08:00 | correr
Registros faltantes:
Fecha de inicio | hora de inicio | Fecha de finalización | hora de finalización | Estado :--------- | :-------- | :--------- | :------- | :------ 2020-03-02 | 00:00:00 | 2020-03-02 | 01:30:00 | detenido 2020-03-02 | 09:00:00 | 2020-03-04 | 04:30:00 | detenido 2020-03-04 | 09:00:00 | 2020-03-04 | 11:30:00 | detenido 2020-03-04 | 19:30:00 | 2020-03-05 | 05:00:00 | detenido 2020-03-05 | 11:15:00 | 2020-03-05 | 12:30:00 | detenido
demostración db<>violín aquí