PostgreSQLのOVERLAPSの罠

PostgreSQLには、二つの時間間隔が重なるかどうかを返すOVERLAPSという便利な演算子があります。

3/1～3/31に対して、

• 3/30～3/31は重なる？

sql

SELECT (DATE '2016-03-01', DATE '2016-03-31') OVERLAPS
       (DATE '2016-03-30', DATE '2016-03-31')
=> true

うんうん。

• 3/30は重なる？

sql

SELECT (DATE '2016-03-01', DATE '2016-03-31') OVERLAPS
       (DATE '2016-03-30', DATE '2016-03-30')
=> true

うんうん。

• 3/1は重なる？

sql

SELECT (DATE '2016-03-01', DATE '2016-03-31') OVERLAPS
       (DATE '2016-03-01', DATE '2016-03-01')
=> true

うんうん。

• 3/31は重なる？

sql

SELECT (DATE '2016-03-01', DATE '2016-03-31') OVERLAPS
       (DATE '2016-03-31', DATE '2016-03-31')
=> false

あれ？

どうやら、(from, to)の日付が同じ場合、それがもう片方の末端にあると重複しないという判定になるようです。

解決法

範囲型のdaterangeを使うと、正しく判定できます。

sql

SELECT daterange(DATE '2016-03-01', DATE '2016-03-31', '[]') &&
       daterange(DATE '2016-03-31', DATE '2016-03-31', '[]')
=> true

最後の'[]'は、開始・終了点がどちらも閉区間であることを指定するものです。

また、範囲型であれば共通部分を取得することもできます。

sql

SELECT daterange(DATE '2016-03-01', DATE '2016-03-31', '[]') *
       daterange(DATE '2016-03-31', DATE '2016-03-31', '[]') 
=> [2016-03-31,2016-04-01)

最後の')'は、開区間であることを示しています。

範囲型を使う上での注意点

共通部分がないことを表すのは、nullでも''などでもなく、emptyです。

sql

SELECT daterange(DATE '2016-03-01', DATE '2016-03-31', '[]') *
       daterange(DATE '2016-04-01', DATE '2016-04-02', '[]') = 'empty'
=> true

この場合のemptyは、daterange型のemptyです。

isempty()という関数も用意されています。

sql

SELECT isempty(daterange(DATE '2016-03-01', DATE '2016-03-31', '[]') *
               daterange(DATE '2016-04-01', DATE '2016-04-02', '[]'))
=> true