SQLにおけるBETWEENの境界値と、カラムの精度に注意

BETWEENの境界値の扱いは、指定した2つの項を含みます

[column] BETWEEN [x] AND [y]

上のクエリーは以下と同等です。

[x] <= [column] AND [column] <= [y]

境界値を含めたくない場合はそれぞれの項で書く必要があります。

[x] < [column] AND [column] = [y]
[x] < [column] AND [column] <= [y]
[x] <= [column] AND [column] < [y]

日付の指定の場合は注意

日付の指定の場合は要注意

例えば、以下は意図しない結果が返る可能性がある。

BETWEEN '2019-01-01 00:00:00' AND '2019-01-31 23:59:59'

百聞は一見にしかず

mysql> select '2019-01-31 23:59:59.100' < '2019-01-31 23:59:59' ;
+---------------------------------------------------+
| '2019-01-31 23:59:59.100' < '2019-01-31 23:59:59' |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)

以下にマニュアルを引用。

DATETIME[(fsp)]
日付と時間の組み合わせです。サポートしている範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' です。MySQL は、'YYYY-MM-DD HH:MM:SS[.fraction]' の形式で DATETIME 値を表示しますが、文字列または数値のどちらかを使用した DATETIME カラムへの値の割り当てを許可しています。
MySQL 5.6.4 以降では、小数秒の精度を指定するために 0 から 6 の範囲でオプションの fsp 値を指定できます。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。
MySQL 5.6.5 以降、DATETIME カラムに対する自動初期化および現在の日時への自動更新は、セクション11.3.5「TIMESTAMP および DATETIME の自動初期化および更新機能」で説明しているように、DEFAULT および ON UPDATE カラム定義句を使用して指定できます。

https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-type-overview.html

MySQLにおけるdatetimeはオプションによって秒より高い精度で保存されている可能性があるので、このクエリーでは意図しない結果が返ってくる可能性がある。論理的に正しいコードを書いていれば問題無いけど。

コードレビューの時に見逃しそうで怖いので、ユニットテストで担保するのが良さそう。

世の中の人はどうしているのか気になってグーグルしてみたら、MySQLだけで6万件ぐらい怪しい記述が出てきた。

一応、手元のレコードを表示してみた。

mysql> select DATE_FORMAT(created_at,'%Y/%m/%d %H:%i:%S.%f') from [table] limit 5;
+------------------------------------------------+
| DATE_FORMAT(created_at,'%Y/%m/%d %H:%i:%S.%f') |
+------------------------------------------------+
| 2018/06/18 04:01:57.000000                     |
| 2018/06/18 04:06:39.000000                     |
| 2018/06/18 04:06:39.000000                     |
| 2018/06/18 04:06:39.000000                     |
| 2018/06/18 04:06:39.000000                     |
+------------------------------------------------+
5 rows in set (0.00 sec)

まとめ

  • 境界値の表現方法と、カラムの精度をちゃんと理解して実装する必要がある。
  • 物理的には正しいが、論理的には間違った実装をしてしまって不正確な集計を行ってしまう場合が出てくる。

matsubokkuri

Please feel free to contact me via e-mail, twitter and facebook!

あわせて読みたい

コメントを残す