count time interval, MySQL

Language: Layout:

drop view if exists overlaped_activities;
drop table if  exists incidentes;
drop table if  exists calendar;
drop procedure if exists filldates;

DROP TABLE IF EXISTS incidentes;
CREATE TABLE IF NOT EXISTS incidentes (
  start datetime DEFAULT NULL,
  end datetime DEFAULT NULL,
  status varchar(20)
);

INSERT INTO incidentes (start, end, status) VALUES
('2019-09-19 12:10:30', '2019-09-19 14:10:30', 'fechado'),
('2019-09-19 22:10:30', '2019-09-20 01:10:30', 'fechado'),
('2019-09-20 01:05:30', '2019-09-20 02:10:30', 'fechado'),
('2019-09-20 01:05:30', null, 'aberto');

CREATE TABLE if not exists calendar (
    mdate date PRIMARY KEY NOT NULL
);

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

DECLARE adate date;

WHILE dateStart <= dateEnd DO

SET adate = (SELECT mdate FROM calendar WHERE mdate = dateStart);

IF adate IS NULL THEN BEGIN

INSERT INTO calendar (mdate) VALUES (dateStart);
           
            END; END IF;

SET dateStart = date_add(dateStart, INTERVAL 1 DAY);

END WHILE;

END;

CALL filldates('2019-09-01','2019-09-22');

create view overlaped_activities
as
SELECT status, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time
FROM (
    SELECT x.status, x.start, min(y.end) AS end 
    FROM incidentes AS x 
    JOIN incidentes AS y 
        ON x.status = y.status
       AND x.start <= y.end 
       AND x.status = 'fechado'
       AND NOT EXISTS (
           SELECT 1 
           FROM incidentes AS z 
           WHERE y.status = z.status 
             AND y.end >= z.start 
             AND y.end < z.end
       ) 
    WHERE NOT EXISTS (
        SELECT 1 
        FROM incidentes AS u 
        WHERE x.status = u.status 
          AND x.start > u.start 
          AND x.start <= u.start
    ) 
    GROUP BY x.status, x.start
) AS v GROUP BY status, end;

#final 
###########################
select status, date_activity, sum(minutes) min_activity, sum(hour) hour_activity
from (
       select status, 
              date(start) date_activity,
              if(date(start) = date(end), time_to_sec(timediff(end, start)) / 60, (1440 - time_to_sec(time(start)) / 60)) minutes,
              if(date(start) = date(end), time_to_sec(timediff(end, start)) / 3600, (24 - time_to_sec(time(start)) / 3600)) hour
       from overlaped_activities
    
       UNION ALL
    
       select status, 
              date(end) date_activity,
              if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) minutes,
              if(date(start) = date(end), 0, time_to_sec(time(end)) / 3600) hour
       from overlaped_activities
    
       UNION ALL
    
       select   status,
                mdate date_activity,
                sum(1440) minutes,
                sum(24) hour
       from     calendar
       join     overlaped_activities
       on       calendar.mdate > date(start)
       and      calendar.mdate < date(end)
       where    datediff(end, start) > 1
       group by status, mdate
    ) act
where date_activity between '2019-09-19' and '2019-09-19'
group by status, date_activity;

drop table if  exists incidentes;
drop table if  exists calendar;
drop view if exists overlaped_activities;
drop procedure if exists filldates;


absolute service time: 0,43 sec