xmr50stQ

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