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
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型)の比較
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型)の比較
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型)の比較
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型)の比較
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 | false | 日時対日付 |
| TIME | error | 時刻対日付なのでエラー |
table2 (TIMESTAMP型)の比較
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型)の比較
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')が有効期限を確認する場合は
SELECT now() BETWEEN start_at AND end_at FROM table1;
は
SELECT '2020-07-31 12:00:00' BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'
と解釈され 範囲外 となってしまいます
正しく比較するためには先に比較元(BETWEEN前)をキャストし、型を揃えておく必要があります。
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なのもおかしいことになります。）