agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: GROUP BY overlapping (tsrange) entries
Date: Fri, 29 Jan 2016 01:05:54 +0100 (CET)
Message-ID: <VisenaEmail.a.59f1cda93d3e2bdc.1528aa70725@tc7-visena> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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_timeTIMESTAMP, tsrange TSRANGE NOT NULL ); CREATE INDEX event_range_idx ON 
event USING gist(tsrange); -- Populate tsrange in this trigger CREATE OR 
REPLACE FUNCTIONevent_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@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;

view thread (15+ messages)  latest in thread

Message-ID: <VisenaEmail.a.59f1cda93d3e2bdc.1528aa70725@tc7-visena>
Permalink:  ../VisenaEmail.a.59f1cda93d3e2bdc.1528aa70725@tc7-visena/
Also on:    postgresql.org/message-id/VisenaEmail.a.59f1cda93d3e2bdc.1528aa70725@tc7-visena

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: andreas@visena.com
  Subject: Re: GROUP BY overlapping (tsrange) entries
  In-Reply-To: <VisenaEmail.a.59f1cda93d3e2bdc.1528aa70725@tc7-visena>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox