SQLの日付におけるBETWEEN句の記述

# SQLの日付におけるBETWEEN句の記述

SQLでテーブル内に有効期間を設定し
現在時刻が有効期限内かを確認するためにBETWEENを利用した際の
型の違いによる動作差異の確認します。

※TimeZoneは今回は無視です。
※start <= n AND n <= end は使いたくないのです。

# 環境
- Windows 10
- Postgres 11

## テーブル定義
### table1
| 名 | 型 |
|---|---|
|start_at|DATE|
|end_at|DATE|

### table2
| 名 | 型 |
|---|---|
|start_at|TIMESTAMP|
|end_at|TIMESTAMP|

### table3
| 名 | 型 |
|---|---|
|start_at|TIME|
|end_at|TIME|

## CREATE TABLE & INSERT
```sql
CREATE TEMP TABLE table1 (start DATE, end DATE);
CREATE TEMP TABLE table2 (start TIMESTAMP, end TIMESTAMP);
CREATE TEMP TABLE table3 (start TIME, end TIME);

INSERT INTO table1 VALUES('2020-07-01 00:00:00', '2020-07-31 23:59:59');
INSERT INTO table2 VALUES('2020-07-01 00:00:00', '2020-07-31 23:59:59');
INSERT INTO table3 VALUES('2020-07-01 00:00:00', '2020-07-31 23:59:59');
```


# 検証

実際に日付、日時、時刻の比較を行っていきます

## 開始の比較

### table1 (DATE型)の比較
```sql
SELECT '2020-07-01 00:00:00'::DATE BETWEEN start_at AND end_at FROM table1;
SELECT '2020-07-01 00:00:00'::TIMESTAMP BETWEEN start_at AND end_at FROM table1;
SELECT '2020-07-01 00:00:00'::TIME BETWEEN start_at AND end_at FROM table1;
```
| 現在日時Cast型 |結果|備考|
|---|---|---|
| DATE | true | 日付対日付|
| TIMESTAMP | true |日時対日付|
| TIME | error |時刻対日付なのでエラー|

### table2 (TIMESTAMP型)の比較
```sql
SELECT '2020-07-01 00:00:00'::DATE BETWEEN start_at AND end_at FROM table2;
SELECT '2020-07-01 00:00:00'::TIMESTAMP BETWEEN start_at AND end_at FROM table2;
SELECT '2020-07-01 00:00:00'::TIME BETWEEN start_at AND end_at FROM table2;
```
| 現在日時Cast型 |結果|備考|
|---|---|---|
| DATE | true | 日付対日時|
| TIMESTAMP | true |日時対日時|
| TIME | error |時刻対日時なのでエラー|

### table3 (TIME型)の比較
```sql
SELECT '2020-07-01 00:00:00'::DATE BETWEEN start_at AND end_at FROM table3;
SELECT '2020-07-01 00:00:00'::TIMESTAMP BETWEEN start_at AND end_at FROM table3;
SELECT '2020-07-01 00:00:00'::TIME BETWEEN start_at AND end_at FROM table3;
```
| 現在日時Cast型 |結果|備考|
|---|---|---|
| DATE | error | 日付対時刻なのでエラー|
| TIMESTAMP | error |日時対時刻なのでエラー|
| TIME | true |時刻対時刻|


## 終了の比較

### table1 (DATE型)の比較
```sql
SELECT '2020-07-31 23:59:59'::DATE BETWEEN start_at AND end_at FROM table1;
SELECT '2020-07-31 23:59:59'::TIMESTAMP BETWEEN start_at AND end_at FROM table1;
SELECT '2020-07-31 23:59:59'::TIME BETWEEN start_at AND end_at FROM table1;
```
| 現在日時Cast型 |結果|備考|
|---|---|---|
| DATE | true | 日付対日付|
| TIMESTAMP | <font color=red>**false**</font> |日時対日付|
| TIME | error |時刻対日付なのでエラー|

### table2 (TIMESTAMP型)の比較
```sql
SELECT '2020-07-31 23:59:59'::DATE BETWEEN start_at AND end_at FROM table2;
SELECT '2020-07-31 23:59:59'::TIMESTAMP BETWEEN start_at AND end_at FROM table2;
SELECT '2020-07-31 23:59:59'::TIME BETWEEN start_at AND end_at FROM table2;
```
| 現在日時Cast型 |結果|備考|
|---|---|---|
| DATE | true | 日付対日時|
| TIMESTAMP | true |日時対日時|
| TIME | error |時刻対日時なのでエラー|

### table3 (TIME型)の比較
```sql
SELECT '2020-07-31 23:59:59'::DATE BETWEEN start_at AND end_at FROM table3;
SELECT '2020-07-31 23:59:59'::TIMESTAMP BETWEEN start_at AND end_at FROM table3;
SELECT '2020-07-31 23:59:59'::TIME BETWEEN start_at AND end_at FROM table3;
```
| 現在日時Cast型 |結果|備考|
|---|---|---|
| DATE | error | 日付対時刻なのでエラー|
| TIMESTAMP | error |日時対時刻なのでエラー|
| TIME | true |時刻対時刻|


# 考察

注目すべき点は
TIMESTAMP型 対 DATE型の終了時の判定です

比較先(BETWEEN後)のDATE型をTIMESTAMP型に暗黙的にキャストする際に `DATE型値 + 00:00:00` の日時に変換されるため  
`'2020-07-31 23:59:59' BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'`
の処理になりFalseが返ってくる結果となってしまいます

このためtable1(DATE型)で、今(now()=例:最終日の昼'2020-07-31 12:00:00')が有効期限を確認する場合は

```sql
SELECT now() BETWEEN start_at AND end_at FROM table1;
```

は

```sql
SELECT '2020-07-31 12:00:00' BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'
```

と解釈され **範囲外** となってしまいます

正しく比較するためには先に比較元(BETWEEN前)をキャストし、型を揃えておく必要があります。

```sql
SELECT now()::DATE BETWEEN start_at AND end_at FROM table1;
```

また根本的に比較先(BETWEEN後)の終了日に'23:59:59'を付与して比較も避けたほうがいいでしょう。
TIMESTAMP型は秒以下の値も持っているため'24:59:59.333'辺りに処理要求が入った場合にFalseとなります。
（そもそも誤差の範囲でFalseでも構わないのですが、厳密には'2020-07-31'なのにFalseなのもおかしいことになります。）