check for overlapping time intervals

From: Wolfgang Meiners <WolfgangMeiners01(at)web(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: check for overlapping time intervals
Date: 2013-04-22 10:19:17
Message-ID: kl32r0$850$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Kellerer 2013-04-22 10:36:22 Re: check for overlapping time intervals
Previous Message Pavel Stehule 2013-04-20 16:59:05 Re: [SQL] Table indexes in a SELECT with JOIN´s