1XLPpj8m

1DROP PROCEDURE if exists Test123;
2DELIMITER //
3CREATE PROCEDURE Test123()
4BEGIN
5   
6
7drop temporary table if exists calc;
8create temporary table if not exists calc as (
9	select
10        event.hosts_id
11        , event.status
12        , greatest('2019-06-01 06:06:25', event.from) as "from"
13        , least('2019-07-01 06:06:25', event.to) as "to"
14    from (
15        select
16            h1.hosts_id
17            , h1.status
18            , h1.timestamp as "from"
19            , ifnull(h2.timestamp,now()) as "to"
20        from usage.history as h1
21        left join usage.history h2 on h1.hosts_id = h2.hosts_id
22            and h2.timestamp = (select min(h3.timestamp)
23                                from usage.history h3
24                                where h3.hosts_id = h2.hosts_id
25                                    and h3.timestamp > h1.timestamp)
26    ) event
27where
28    event.from >= ifnull((select max(timestamp)
29                            from usage.history
30                            where hosts_id = event.hosts_id
31                                and timestamp <= '2019-06-01 06:06:25'), '1970-1-01')
32        and
33          event.to <= ifnull((select min(timestamp)
34                                from usage.history
35                                where hosts_id = event.hosts_id
36                                    and timestamp >= '2019-07-01 06:06:25'), now())
37);
38
39SELECT 
40    hosts_id,
41    status,
42    SUM(TIMESTAMPDIFF(SECOND,
43        calc.from,
44        calc.to)) AS time
45FROM
46    calc
47WHERE
48    calc.from <= calc.to
49GROUP BY hosts_id , status;
50
51END //
52DELIMITER ;
53
54call Test123();
55select * from calc