POSTGRESQL group by for interval

Question

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

Could you post the example over here in plain text? External images disappear and make this post useless for others in the future — Frank Heikens, Jan 05 '21 at 10:36

score 0 · Accepted Answer · answered Jan 05 '21 at 18:19

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

Working fiddle

POSTGRESQL group by for interval

1 Answers1