A data type that represents a range of values. Optionally with upper/lower bounds inlcuded or excluded. This is not part of the SQL standard

create table room_reservation
(
   reservation_id   integer    not null primary key,
   room_id          integer    not null references rooms,
   booked_between   tsrange    not null -- defines a time "range" when this room is booked
);

insert into room_reservation 
  (reservation_id, room_id, booked_between)
values 
  (1, 1, tsrange(timestamp '2014-09-08 10:00:00', timestamp '2014-09-08 18:00:00', '[)'), -- room 1 is booked from 10:00 to 18:00 (exclusive) on September 8th
  (2, 1, tsrange(timestamp '2014-09-08 18:00:00', timestamp '2014-09-08 22:00:00', '[)'), -- room 1 is booked from 18:00 to 22:00 on September 8th

Using ranges it's quite easy to find rooms that are booked in a specific timerange:

select *
from room_reservation
where booked_between && tsrange(timestamp '2014-09-08 11:00:00', timestamp '2014-09-08 14:00:00', '[)')

The following query finds all rooms that are available between 17:00 and 18:30

select r.*
from rooms r
where not exists (select 1
                  from room_reservation rv 
                  where rv.room_id = r.room_id 
                    and rv.booked_between && tsrange(timestamp '2014-09-08 17:00:00', timestamp '2014-09-08 18:30:00', '[)'))

PostgreSQL is currently the only DBMS that implements range types

Back to the SQL Feature Comparison