Time in SQL

If you’ve built an application, it probably included some time/date fields. If so, let this get you thinking (&& means “overlap”):

CREATE TABLE b (p PERIOD);
ALTER TABLE b
   ADD EXCLUDE USING gist (p WITH &&);
INSERT INTO b
   VALUES('[2009-01-05, 2009-01-10)');
INSERT INTO b
   VALUES('[2009-01-07, 2009-01-12)'); -- ERROR

Postgres 9 added Exclusion Constraints opening up more ways to specify UNIQUE constraints on a table. Adding in the temporal “period” datatype for Postgres, you can create a constraint that prevents any two periods from overlapping. There’s simply no way to enforce that with a constraint without that support so most of the time we enforce it using application logic.

Let’s take for example an application that schedules professors to classrooms. No two professors can teach in the same classroom at the same time: there must not be any overlap. Here’s a couple lines of SQL that would prevent invalid data:

CREATE TABLE reservation(room TEXT
        , professor TEXT
        , during PERIOD);

-- enforce the constraint that the
-- room is not double-booked
ALTER TABLE reservation
    ADD EXCLUDE USING gist
    (room WITH =, during WITH &&);

-- enforce the constraint that the
-- professor is not double-booked
ALTER TABLE reservation
    ADD EXCLUDE USING gist
   (professor WITH =, during WITH &&);

The reservation table has two exclusion constraints on it which prevent overlap for any single room and any single professor. The net result is that room-professor bookings are guaranteed unique no matter how long a given classroom is booked (some classes might be 60 minutes and others might go all day).

Combined with the Developing Time-Oriented Database Applications in SQL, this makes for some interesting brainstorming. Also read the Exclusion Constraints post.