SQL:日付と時刻に列が分かれているテーブルで日時を範囲指定したい

背景

日時が日付と時刻に列が分かれているテーブルがあります。where条件で日時を範囲指定する際、気を付けないと必要なレコードが漏れてしまいます。

日付と時刻の列が分かれたテーブル　wt_tbl

C:\qiita>db2 select * from wt_tbl order by wt_date, wt_time

USR_ID      WT_DATE    WT_TIME  WT_KG
----------- ---------- -------- -------
          1 2024-07-12 10:23:14    53.3
          2 2024-07-12 15:11:19    63.6
          3 2024-07-13 11:03:11    58.5
          4 2024-07-13 14:45:01    73.3
          5 2024-07-14 09:42:31    83.6
          6 2024-07-14 16:55:21    67.9

  6 レコードが選択されました。

やりたいこと

• 対象が漏れないように範囲指定したい
• 行値式を使ってSQLを簡潔に記述したい

IBM Db2 V11.5 Windowsで確認しています。行値式はSQL Serverが使用できないという情報がありますが、SQL92に準拠しているDBMSならできるはずです。

誤った検索条件

2024年7月12日12:00:00以降のレコードを範囲指定したい。以下の記述は誤りです。

case01.sql

select * from wt_tbl 
  where wt_date >= '2024-07-12' 
    and wt_time >= '12:00:00' 
  order by wt_date, wt_time ;

すべての日付で12:00:00より前のレコードが漏れています。

実行結果

C:\qiita>db2 -tf case01.sql

USR_ID      WT_DATE    WT_TIME  WT_KG
----------- ---------- -------- -------
          2 2024-07-12 15:11:19    63.6
          4 2024-07-13 14:45:01    73.3
          6 2024-07-14 16:55:21    67.9

  3 レコードが選択されました。

正しい検索条件

7月12日で12:00:00以降と7月12日より後をor条件で範囲指定します。

case02.sql

select * from wt_tbl 
  where (wt_date = '2024-07-12' and wt_time >= '12:00:00') 
     or  wt_date > '2024-07-12' 
  order by wt_date, wt_time ;

2024年7月12日12:00:00以降のレコードがselectされます。

実行結果

C:\qiita>db2 -tf case02.sql

USR_ID      WT_DATE    WT_TIME  WT_KG
----------- ---------- -------- -------
          2 2024-07-12 15:11:19    63.6
          3 2024-07-13 11:03:11    58.5
          4 2024-07-13 14:45:01    73.3
          5 2024-07-14 09:42:31    83.6
          6 2024-07-14 16:55:21    67.9

  5 レコードが選択されました。

行値式を使う方法

行値式を使うと簡潔に記述できます。

case03.sql

select * from wt_tbl 
  where (wt_date, wt_time) >= ('2024-07-12', '12:00:00') 
  order by wt_date, wt_time ;

case02.sqlと同じ内容、正しい結果がselectされます。

実行結果

C:\qiita>db2 -tf case03.sql

USR_ID      WT_DATE    WT_TIME  WT_KG
----------- ---------- -------- -------
          2 2024-07-12 15:11:19    63.6
          3 2024-07-13 11:03:11    58.5
          4 2024-07-13 14:45:01    73.3
          5 2024-07-14 09:42:31    83.6
          6 2024-07-14 16:55:21    67.9

  5 レコードが選択されました。

betweenで行値式

行値式はbetweenにも使えます。2024年7月12日12:00:00から2024年7月14日12:00:00までを範囲指定します。

case04.sql

select * from wt_tbl 
  where (wt_date, wt_time) between ('2024-07-12', '12:00:00') and ('2024-07-14', '12:00:00') 
  order by wt_date, wt_time ;

FromToで正しい結果がselectされます。

実行結果

C:\qiita>db2 -tf case04.sql

USR_ID      WT_DATE    WT_TIME  WT_KG
----------- ---------- -------- -------
          2 2024-07-12 15:11:19    63.6
          3 2024-07-13 11:03:11    58.5
          4 2024-07-13 14:45:01    73.3
          5 2024-07-14 09:42:31    83.6

  4 レコードが選択されました。

WT_TIMEの値にNULLがある場合、ifnull関数で'00:00:00'をセットすることも可能です。この場合、NULLは'00:00:00'として取り扱われます。

case04null.sql

select * from wt_tbl 
  where (wt_date, ifnull(wt_time, '00:00:00')) between ('2024-07-12', '12:00:00') and ('2024-07-14', '12:00:00') 
  order by wt_date, wt_time ;

パフォーマンス

行値式で記述しているcase3.sqlの実行計画（エクスプレイン）を見てみましょう。オリジナルステートメント(Original Statement)に行値式で記述されているSQLが最適化ステートメント(Optimized Statement)ではor条件に変換されています。これはcase2.sqlと同じです。このことから行値式で記述しても内部的にはor条件と同じSQLになるので、パフォーマンス向上のために行値式で書き直すことは意味がありません。

case03.sqlの実行計画（抜粋）

Original Statement:
------------------
select 
  * 
from 
  wt_tbl 
where 
  (wt_date, wt_time) >= ('2024-07-12', '12:00:00') 
order by 
  wt_date,
  wt_time


Optimized Statement:
-------------------
SELECT 
  Q1."USR_ID" AS "USR_ID",
  Q1."WT_DATE" AS "WT_DATE",
  Q1."WT_TIME" AS "WT_TIME",
  Q1."WT_KG" AS "WT_KG" 
FROM 
  DB2ADMIN.WT_TBL AS Q1 
WHERE 
  ((Q1."WT_DATE" > '2024-07-12') OR 
   ((Q1."WT_DATE" = '2024-07-12') AND 
    (Q1."WT_TIME" >= '12:00:00'))) 
ORDER BY 
  Q1."WT_DATE",
  Q1."WT_TIME"

まとめ

• 日付と時刻に列が分かれているテーブルは誤った検索条件を記述しがち
• 行値式で簡潔な記述が可能
• 行値式は内部的にはor条件のSQLになるのでパフォーマンスは同じ