Help us understand the problem. What is going on with this article?

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

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なのもおかしいことになります。)

qiitadon
Qiitadon(β)から生まれた Qiita ユーザー・コミュニティです。
https://qiitadon.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away