Postgres 9.2具有范围类型，我建议使用它们。
create table reservation (reservation tsrange);
insert into reservation values
('[2012-11-14 09:00:00,2012-11-14 10:00:00)'),
('[2012-11-14 10:00:00,2012-11-14 11:30:00)'),
('[2012-11-14 12:00:00,2012-11-14 14:00:00)'),
('[2012-11-14 16:00:00,2012-11-14 18:30:00)');
ALTER TABLE reservation ADD EXCLUDE USING gist (reservation WITH &&);
“ EXCLUDE USING gist”创建不允许插入重叠条目的索引。您可以使用以下查询找到差距（vyegorov查询的变体）：
with gaps as (
select
upper(reservation) as start,
lead(lower(reservation),1,upper(reservation)) over (ORDER BY reservation) - upper(reservation) as gap
from (
select *
from reservation
union all values
('[2012-11-14 00:00:00, 2012-11-14 08:00:00)'::tsrange),
('[2012-11-14 18:00:00, 2012-11-15 00:00:00)'::tsrange)
) as x
)
select * from gaps where gap > '0'::interval;
“统一所有值”掩盖了非工作时间，因此您只能在上午8点至晚上18点之间进行预订。
结果如下：
start | gap
---------------------+----------
2012-11-14 08:00:00 | 01:00:00
2012-11-14 11:30:00 | 00:30:00
2012-11-14 14:00:00 | 02:00:00
文档链接：-http://www.postgresql.org/docs/9.2/static/rangetypes.html “范围类型” -https://wiki.postgresql.org/images/7/73/Range-types-pgopen- 2012.pdf
602392714
清零编程群