BLOG
ブログ
2022/07/25
技術系
【MySQL】DATETIME型をDATEで検索するときの注意点
タグ
初歩的なことですが、最近遭遇したので残しておきます。
サンプルデータ
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29 |
+-----------+
mysql> DESC tbl;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| sample_date | datetime | YES | MUL | NULL | |
| sample_txt | text | YES | | NULL | |
+-------------+----------+------+-----+---------+----------------+
mysql> SELECT * FROM tbl;
+----+---------------------+------------+
| id | sample_date | sample_txt |
+----+---------------------+------------+
| 1 | 2022-05-31 12:00:00 | -1 day |
| 2 | 2022-06-01 11:00:00 | -1 hour |
| 3 | 2022-06-01 12:00:00 | now |
| 4 | 2022-06-01 13:00:00 | +1 hour |
| 5 | 2022-06-02 12:00:00 | +1 day |
+----+---------------------+------------+「sample_dateが2022/06/01以前」のデータがほしいとき、DATEで検索してしまうと思うような結果になりません。
mysql> SELECT * FROM tbl WHERE sample_date <= '2022-06-01';
+----+---------------------+------------+
| id | sample_date | sample_txt |
+----+---------------------+------------+
| 1 | 2022-05-31 12:00:00 | -1 day |
+----+---------------------+------------+DATEに’00:00:00’が追加されて比較されるためです。
mysql> SELECT CAST('2022-06-01' AS DATETIME);
+--------------------------------+
| CAST('2022-06-01' AS DATETIME) |
+--------------------------------+
| 2022-06-01 00:00:00 |
+--------------------------------+明示的な変換を使用して暗黙的な変換をオーバーライドできます。 たとえば、DATE および DATETIME 値の比較で、DATE 値は、’00:00:00′ の時間部分を追加することにより、強制的に DATETIME 型に変更されます。 代わりに DATETIME 値の時間部分を無視して比較を実行するには、次の方法で CAST() 関数を使用します。
11.2.7 日付と時間型間での変換
解決策
CAST()を使用してDATEとして比較する
mysql> SELECT * FROM tbl WHERE CAST(sample_date AS DATE) <= '2022-06-01';
+----+---------------------+------------+
| id | sample_date | sample_txt |
+----+---------------------+------------+
| 1 | 2022-05-31 12:00:00 | -1 day |
| 2 | 2022-06-01 11:00:00 | -1 hour |
| 3 | 2022-06-01 12:00:00 | now |
| 4 | 2022-06-01 13:00:00 | +1 hour |
+----+---------------------+------------+- ’23:59:59’をつけてDATETIMEとして比較する
mysql> SELECT * FROM tbl WHERE sample_date <= '2022-06-01 23:59:59';
+----+---------------------+------------+
| id | sample_date | sample_txt |
+----+---------------------+------------+
| 1 | 2022-05-31 12:00:00 | -1 day |
| 2 | 2022-06-01 11:00:00 | -1 hour |
| 3 | 2022-06-01 12:00:00 | now |
| 4 | 2022-06-01 13:00:00 | +1 hour |
+----+---------------------+------------+補足
CAST()を使用するとインデックスが効かなくなるので、場合によって使い分けると良いかと思います。
mysql> EXPLAIN SELECT * FROM tbl WHERE CAST(sample_date AS DATE) <= '2022-06-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> EXPLAIN SELECT * FROM tbl WHERE sample_date <= '2022-06-01 23:59:59';
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tbl | NULL | range | idx_1 | idx_1 | 6 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+BINARY、CAST() または CONVERT() を使用してインデックス付けされたカラムを変換すると、MySQL でインデックスを効率的に使用できない場合があることに注意してください。
12.11 キャスト関数と演算子
’23:59:59’についても、うるう秒まで厳密に考慮したい場合はUNIX_TIMESTAMP()を使うと良いようです。
タイムゾーンのうるう秒のサポート
5.1.15 MySQL Server でのタイムゾーンのサポート
株式会社ウイングドアは福岡のシステム開発会社です。
現在、私達と一緒に"楽しく仕事が出来る仲間"として、新卒・中途採用を絶賛募集しています！
ウイングドアの仲間達となら楽しく仕事できるかも？と興味をもった方、
お気軽にお問い合わせ下さい！