以前「日付で期間指定するときに「23:59:59.999」を使わない」なる記事をAPI設計の視点で書いたことがありますが、これはRDBのスキーマ設計・クエリ設計に関しても同じことが言えますので OVERLAPS 述語の紹介も加えて改めて。
- 終了日時を表すカラムに「23:59:59.99...」を格納する形式にせず、翌日の「00:00:00」を格納する設計にする
- 日時範囲を指定して問い合わせるクエリで、範囲末を「23:59:59.99...」で指定する形式にせず、翌日の「00:00:00」を指定する形式にする
です。
その理由は前掲のリンク先の内容ともまったく共通するのですが以下の2点です。
- 「23:59:59」のような記述の精度がDBの日時型の精度と一致していなければ、データの取りこぼしや二重取得を発生させる
- DBの日時型の精度と一致していたとしても、精度は何桁かという処理系知識に強く依存したコードになり読み手に無用な負担をかける
「終了日時を翌日の00:00:00で表す」というのはもう少し一般化した表現をすると 期間は左閉右開の半開区間（＝期間頭以上・期間末未満の組）で表現する ことだと言えます。
これはAPI設計・RDB設計にとどまらない普遍的な原則となるわけですが、RDBにおいては、次のような注意点が加わります。
- 日付時刻の範囲判定に
BETWEEN述語を使わない
BETWEEN述語を使う場合の弊害
SQLで x BETWEEN a AND b は a <= x AND x <= b と等価です。
終了日時を含む判定をしてしまうため、
:some_datetime BETWEEN start_time AND end_time
という判定式は終了日翌日の00:00:00も引っかけてしまいます。
先週分、先々週分、と期間を分けながらレコードを取得するなどしたときにわずかながら二重取得されるレコードが出てくるわけです。
よって、見た目のスマートさで劣るのですが以下の形で記述するのが漏れのない問い合わせとなります。
(start_time <= :some_datetime AND :some_datetime < end_time)
OVERLAPS述語
上記の正しい書き方では :some_datetime というプレースホルダを2回書くことになりました。
1 wordで書けるものなら2回書くことも特にためらわれるものではないのですが、これがもう少し複雑な計算式だった場合にはどうしましょう。
可読性は悪くなりますし、後に片方を修正したときにもう片方の修正を忘れる、ミスるといったリスクも抱えることになります。
今回紹介しますSQL標準の OVERLAPS 述語を使うと2重記述が避けられます。
Oracle, PostgreSQL, DB2以外は採用していないマイナーな述語なのが残念ですが。
(start_time, end_time) OVERLAPS (:some_datetime, INTERVAL'0'DAY)
OVERLAPS 述語は本来、二つの日時範囲（もちろん、左閉右開の半開区間）が重なっているかを判定するものです。
ただし、長さがゼロの範囲を渡すことである単一の日時が範囲に含まれるかの判定にも使えるというもの。
詳細な仕様を述べますと、 (S1, Ｔ１) OVERLAPS (S2, T2) とは
( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )
であると定義されています（S>T だったり S IS NULL だったときには SとTを交換して読みます）。
( T1 <> T2 OR T1 = T2 ) などという一見無意味な項が含まれていますが、これが無意味でないのは各オペランドがNULLである可能性があるからです。
ところが実際 NULL を与えてみるとRDB製品によって異なる結果が出る場合があります。
例えばOracleは本来NULL判定されるべき次の式をFALSEと判定します。
(DATE'2018-01-02', NULL) OVERLAPS (DATE'2018-01-03', DATE'2018-01-04')
NULLが渡る可能性のある式ではRDBの非標準動作に注意する必要があるという但し書きが付きますが、期間に含まれるかの判定・期間が重なるかの判定にはOVERLAPSの使用もご検討くださいませ。
コード意図を明確にしつつ、左閉右開という良い期間定義方法を強制してくれます。