経緯
「パラメータで渡す開始日時・終了日時の期間が、テーブルのtimestamp型のカラムに入っている開始日時・終了日時の期間と重複しているか確認するSQLを書きたいんですが...」
（これ、PostgreSQL勉強会＠札幌でやったとこだ！）
というわけでミニサンプル書いておく。
ミニサンプル
やりたいこと
create table bar (
bar_id bigserial,
open_at timestamp without time zone,
close_at timestamp without time zone,
primary key (bar_id)
);
-- 10月1日0時（以上）から10月2日0時（未満）を比較対象に見立てる
insert into bar(open_at, close_at)
values ('2014-10-01 00:00:00', '2014-10-02 00:00:00' );
こんなテーブルがあったときに、パラメータで渡される他のopen_at, close_atと重複しているレコードがあるかどうか調べたい。
方法
tsrange関数で、レコードとパラメータを範囲型に変換して、&&演算子で比較する方法が一番わかりやすいかなと思う。
-- 9月30日12時から10月1日0時までの重複を確認（重複しない想定）
select name
from (
select bar_id, strange (open_at, close_at, '[)') as range from bar
) as A
where range && tsrange('2014-09-30 12:00:00'::timestamp, '2014-10-01 00:00:00'::timestamp, '[)')
-- 9月30日12時から10月1日12時までの重複を確認（重複する想定）
select bar_id
from (select bar_id, tsrange(open_at, close_at, '[)') as range from bar) as A
where range && tsrange('2014-09-30 12:00:00'::timestamp, '2014-10-01 12:00:00'::timestamp, '[)')
-- 10月1日0時から10月2日12時までの重複を確認（重複する想定）
select bar_id
from (select bar_id, tsrange(open_at, close_at, '[)') as range from bar) as A
where range && tsrange('2014-10-01 00:00:00'::timestamp, '2014-10-02 12:00:00'::timestamp, '[)')
-- 10月2日0時から10月2日12時までの重複を確認（重複しない想定）
select bar_id
from (select bar_id, tsrange(open_at, close_at, '[)') as range from bar) as A
where range && tsrange('2014-10-02 00:00:00'::timestamp, '2014-10-02 12:00:00'::timestamp, '[)')
重複があればbar_idカラムの値が検索される。
今回は open_at以上〜close_at未満で比較したいので、境界値は'[)'を利用しているけれども、境界値を変更すると、もちろん重複結果も変えることができる。
参考文献
範囲型の説明は以下が詳しい。