Re: GROUP BY overlapping (tsrange) entries

It is unclear to me how you got from your input data to your expected
output. If you are "trying to count() overlapping entries" then it would
seem to me that you would only have only one value for the count. Either a
range overlaps or it does not.

On Thu, Jan 28, 2016 at 4:05 PM, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:

> Hi all.
>
> I'm trying to count() overlapping entries (timestamp-ranges, tsrange) and
> have the following test-data:
>
>
> create table event(
> id SERIAL PRIMARY KEY,
> start_time timestamp NOT NULL,
> end_time TIMESTAMP,
> tsrange TSRANGE NOT NULL);
> CREATE INDEX event_range_idx ON event USING gist (tsrange);
>
> -- Populate tsrange in this triggerCREATE OR REPLACE FUNCTION event_update_tf() returns TRIGGER AS $$BEGIN if NEW.end_time IS NOT NULL then NEW.tsrange = tsrange(NEW.start_time, NEW.end_time, '[]'); else NEW.tsrange = tsrange(NEW.start_time, null, '[)'); end if; RETURN NEW;END;$$ LANGUAGE plpgsql;
> CREATE TRIGGER event_update_t BEFORE INSERT OR UPDATE ON eventFOR EACH ROW EXECUTE PROCEDURE event_update_tf();
> insert into event(start_time, end_time)
> values('2015-12-20', NULL)
> , ('2015-12-20', '2015-12-31')
> , ('2015-12-25', '2016-01-01')
> , ('2015-11-20', '2015-11-24')
> , ('2016-02-01', '2016-02-03')
> , ('2016-02-01', '2016-02-04')
> , ('2016-02-01', NULL)
> ;
>
>
> What I'd like is output like this:
>
> count
> ───────
> 1
> 3
> 3
> (3 rows)
>
> Something like:
> SELECT count(*) FROM event group by (tsrange with &&);
>
> PS: In my real query the tsrange and other data is the result of a query
> involving multile tables, this is just a simplified example to deal with
> the "group by tsquery"
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>