Re: GROUP BY overlapping (tsrange) entries

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 trigger
CREATE 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 event
FOR 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@visena.com

www.visena.com