SQLで日付範囲の重なりチェックをする

何番煎じかわからないですが、自分用のメモ

予約のチェックのために 「指定した期間中に予約のあるレコードを抽出する」
などをしたいことがあります。

この時、レコード側の予約期間と 検索条件の日付期間をつきあわせてほしいものを取得することになります。

## 先に結論
先に結論を書くと

```sql
where
    レコードの開始日付 <= 検索条件の終了日付
and レコードの終了日付 >= 検索条件の開始日付 
```
でとれます。

もちろん仕様によっては 日付だけでなく 時刻も入ってきたり するので
`<=` や `>=` ではなく `<` や `>` にしたり、検索条件の日付を +1 したりする場合もあります。

あとnullが絡んでくる場合はその対応も必要です。

今回は 日付部分だけの単純パターンです。

## 考え方
データベース側のテーブルデータと、検索条件の関係を考えると
以下のパターンが考えられます

![001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/628749/18738cec-054b-3411-e046-f66788ff65ce.png)


1. 検索期間がレコード期間より前
1. 検索期間がレコードの開始を含む
1. 検索期間がレコードの期間に含まれる
1. 検索期間がレコードの終了を含む
1. 検索期間がレコード期間より後
1. 検索期間がレコードの期間を含む

文章で説明を書いてみましたが イメージでとらえたほうがわかりやすいかと思います。

このうち、期間の重なりがあるのは 赤色で示した部分になります。

緑色の部分の否定で取りに行く方法もあるかもしれないですが、今回は普通に赤色の部分を抽出しにいきます。

### レコードの終了日付に注目
![002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/628749/9771b4f9-ac92-ff32-1a5c-892ba56093f7.png)


レコード側の終了日付に着目します。

すると 検索条件の開始日付はいずれも 左側(つまり以前)にあることがわかります。

これをクエリで表現すると

```sql
検索条件の開始日付 <= レコードの終了日付
```
となります。

私は 左辺にテーブルの列を書くことが多いので左右を入れ替えて

```sql
レコードの終了日付 >= 検索条件の開始日付
```
としておきます。

これだと 1が余計ですね


### レコードの開始日付に注目
![003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/628749/5151c688-2699-e963-5eb7-1e185018635d.png)

レコード側の開始日付に着目します。

すると 検索条件の終了日付はいずれも 右側(つまり以降)にあることがわかります。

これをクエリで表現すると

```sql
レコードの開始日付 <= 検索条件の終了日付
```

これだと 5が余計ですね

### 両方の条件を組み合わせる

1, 5 は片方にしか存在しないのでAND で組み合わせることで 範囲の重なりをとることができます。

## 実際に試してみる

実際に試してみます。
データベースは `H2 Database` を使いました。

### テーブル

こんな感じの `BOOKING` テーブルを用意します。

|列名        | 型名       |メモ     |
|-----------|------------|---------|
|BOOKING_ID | VARCHAR(6) | 主キー  |
|START_DATE | DATE       | not null|
|END_DATE   | DATE       | not null|

```sql
create table BOOKING (
   BOOKING_ID  VARCHAR(6)
  ,START_DATE  DATE not null
  ,END_DATE    DATE not null
  ,constraint PK_BOOKING primary key (
    BOOKING_ID
  )
);
```

データは1件だけ用意します。
検索条件の方をいろいろ変化させることで 確認してみます。

|BOOKING_ID | START_DATE |END_DATE   |
|-----------|------------|-----------|
|000001     |2021-01-01  |2021-01-31 |

```sql
insert into BOOKING values ('000001', DATE'2021-01-01', DATE'2021-01-31');
```

### 実行
以下のようなクエリを実行してみます。
`with` を使っているのでわかりにくいですが `SEARCH_CONDITION` が検索条件で `SEARCH_ID` が今回の条件の番号です。

`BOOKING` 側ではなく、重なった検索条件の方を結果に表示するようにしています。

```sql
with
  -- 変化させる検索条件を6パターン用意
  SEARCH_CONDITION(SEARCH_ID, SEARCH_FROM, SEARCH_TO) as (
    select 1, '2020-12-01', '2020-12-31' union all
    select 2, '2020-12-15', '2021-01-15' union all
    select 3, '2021-01-10', '2021-01-20' union all
    select 4, '2021-01-15', '2021-02-15' union all
    select 5, '2021-02-15', '2021-02-28' union all
    select 6, '2020-12-01', '2021-02-28'
)
select
   C.SEARCH_ID
  ,C.SEARCH_FROM
  ,C.SEARCH_TO
from
  BOOKING B
cross join
  SEARCH_CONDITION C
where
    B.START_DATE <= TO_DATE(C.SEARCH_TO, 'YYYY-MM-DD')
and B.END_DATE   >= TO_DATE(C.SEARCH_FROM, 'YYYY-MM-DD')
order by
  C.SEARCH_ID
```

検索結果


|SEARCH_ID |SEARCH_FROM |SEARCH_TO  
|----------|------------|----------
|2         |2020-12-15  |2021-01-15
|3         |2021-01-10  |2021-01-20
|4         |2021-01-15  |2021-02-15
|6         |2020-12-01  |2021-02-28

2, 3, 4, 6が結果に上がってきているので正しくチェックできていることが確認できました。