I have a query in which I have the time of light switches turned on in a flat, with switch on (start) time and switch off (end) time.
I would to know how many light switches are on, does anyone know how can I do?
Example query:
Select Flat, ID_light, start, end, 1 as ON
from lights
An example of i/o:
Input data:
Flat Id Light Start End On
1 1 01/01/2021 00:00:15 01/01/2021 00:59:00 1
1 2 01/01/2021 00:00:15 01/01/2021 01:59:00 1
2 1 01/01/2021 00:00:15 01/01/2021 00:01:15 1
2 1 01/01/2021 00:02:00 01/01/2021 01:00:00 1
2 2 01/01/2021 00:00:00 01/01/2021 08:00:00 1
Text output:
Flat Start End Lights On
1 01/01/2021 00:00:15 01/01/2021 00:59:00 2
1 01/01/2021 00:59:00 01/01/2021 01:59:00 1
2 01/01/2021 00:00:00 01/01/2021 00:00:15 1
2 01/01/2021 00:00:15 01/01/2021 00:01:15 2
2 01/01/2021 00:01:15 01/01/2021 00:02:00 1
2 01/01/2021 00:02:00 01/01/2021 01:00:00 2
2 01/01/2021 01:00:00 01/01/2021 08:00:00 1
The range data type simplifies this.
Get all flats and times:
with times as (
select flat, start as ts from lights
union
select flat, end_ from lights
),
Construct rows for each interval using lead():
all_ivals as (
select flat, ts as start,
lead(ts) over (partition by flat
order by ts) as end_
from times
)
Join back to the original table and aggregate using the range overlap && operator:
select a.flat, a.start, a.end_, count(*) as lights_on
from all_ivals a
join lights l
on tsrange(l.start, l.end_, '[)') && tsrange(a.start, a.end_, '[)')
and l.flat = a.flat
where a.end_ is not null
group by a.flat, a.start, a.end_
order by a.flat, a.start
;
Result:
flat start end_ lights_on
1 2021-01-01 00:00:15 2021-01-01 00:59:00 2
1 2021-01-01 00:59:00 2021-01-01 01:59:00 1
2 2021-01-01 00:00:00 2021-01-01 00:00:15 1
2 2021-01-01 00:00:15 2021-01-01 00:01:15 2
2 2021-01-01 00:01:15 2021-01-01 00:02:00 1
2 2021-01-01 00:02:00 2021-01-01 01:00:00 2
2 2021-01-01 01:00:00 2021-01-01 08:00:00 1
7 rows