check for overlapping time intervals

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

check for overlapping time intervals
3+ messages / 2 participants
[nested] [flat]

* check for overlapping time intervals
@ 2013-04-22 10:19  Wolfgang Meiners <WolfgangMeiners01@web.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Wolfgang Meiners @ 2013-04-22 10:19 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I am on postgresql 9.1 and use at table like

CREATE TABLE timetable(
 tid   INTEGER PRIMARY KEY,
 gid   INTEGER REFERENCES groups(gid),
 day   DATE,
 s     TIME NOT NULL, --- start
 e     TIME NOT NULL, --- end
 CHECK (e > s));

Now, i need a constraint to prevent overlapping timeintervals in this
table. For this, i use a trigger:

CREATE OR REPLACE FUNCTION validate_timetable() RETURNS trigger AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
  IF EXISTS(
   SELECT * FROM timetable
   WHERE gid = NEW.gid AND day = NEW.day
   AND s < NEW.e AND e > NEW.s)
   THEN
    RAISE EXCEPTION 'overlapping intervals';
   END IF;
 ELSIF TG_OP = 'UPDATE' THEN
  IF EXISTS(
   SELECT * FROM timetable
   WHERE gid = NEW.gid AND day = NEW.day
   AND tid <> OLD. tid
   AND s < NEW.e AND e > NEW.s)
   THEN
    RAISE EXCEPTION 'overlapping intervals';
   END IF;
 END IF;
 RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER validate_timetable
BEFORE INSERT OR UPDATE ON timetable
FOR EACH ROW EXECUTE PROCEDURE
validate_timetable();

Is there a simpler way to check for overlapping timeintervals? I ask
this question, because i have more similar tables with similar layout
and would have to write similar functions again and again.

Thank you for any hints
Wolfgang



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: check for overlapping time intervals
@ 2013-04-22 10:36  Thomas Kellerer <spam_eater@gmx.net>
  parent: Wolfgang Meiners <WolfgangMeiners01@web.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Thomas Kellerer @ 2013-04-22 10:36 UTC (permalink / raw)
  To: pgsql-sql

Wolfgang Meiners, 22.04.2013 12:19:
> Is there a simpler way to check for overlapping timeintervals? I ask
> this question, because i have more similar tables with similar layout
> and would have to write similar functions again and again.

Do you have the possibility to upgrade to 9.2?

The range types introduced with 9.2 seem to be *exactly* what you need.

Regards
Thomas





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: check for overlapping time intervals
@ 2013-04-22 18:06  Wolfgang Meiners <WolfgangMeiners01@web.de>
  parent: Thomas Kellerer <spam_eater@gmx.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Wolfgang Meiners @ 2013-04-22 18:06 UTC (permalink / raw)
  To: pgsql-sql

Am 22.04.13 12:36, schrieb Thomas Kellerer:
> Wolfgang Meiners, 22.04.2013 12:19:
>> Is there a simpler way to check for overlapping timeintervals? I ask
>> this question, because i have more similar tables with similar layout
>> and would have to write similar functions again and again.
> 
> Do you have the possibility to upgrade to 9.2?
> 
> The range types introduced with 9.2 seem to be *exactly* what you need.
> 
> Regards
> Thomas
> 

Hi Thomas,

after looking a bit i found you are right. There may be some hacks to do
it without range types, but with range types it is straigt forward to
implement. I am not quite happy to upgrade to 9.2 because i just got
everything running fine on 9.1. But i think, i will do it this way.
Thank you for your help.

Wolfgang





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2013-04-22 18:06 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-04-22 10:19 check for overlapping time intervals Wolfgang Meiners <WolfgangMeiners01@web.de>
2013-04-22 10:36 ` Thomas Kellerer <spam_eater@gmx.net>
2013-04-22 18:06   ` Wolfgang Meiners <WolfgangMeiners01@web.de>

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