MySQLの日付(Date)型を比較する際の注意

384 文字

2 分

MySQLの日付(Date)型を比較する際の注意

2010-07-27

MySQL

DATETIME

/

MySQL

column BETWEEN A AND B イコール A <= column and column <= B なので、以下のSQLの結果は同一かと思ったが、一部の条件で挙動が異なった。

# andの後ろの項を含んでほしいのですが、含んでいない様子。
mysql> select '2010-07-26 00:00:00' BETWEEN '2010-07-25' AND '2010-07-26';
+-------------------------------------------------------------+
| '2010-07-26 00:00:00' BETWEEN '2010-07-25' AND '2010-07-26' |
+-------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

# castをすれば含んでくれる
mysql> select cast('2010-07-26 00:00:00' as datetime) BETWEEN '2010-07-25' AND '2010-07-26 00:00:00';
+----------------------------------------------------------------------------------------+
| cast('2010-07-26 00:00:00' as datetime) BETWEEN '2010-07-25' AND '2010-07-26 00:00:00' |
+----------------------------------------------------------------------------------------+
|                                                                                      1 |
+----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

なぜかというと、ただのクウォートだと文字として比較してしまうから。
比較対象のカラムは型を意識しなければならない。

mysql> SELECT CAST('2010-07-26 00:00:00' AS DATETIME) BETWEEN '2010-07-25' AND '2010-07-26';
+-------------------------------------------------------------------------------+
| CAST('2010-07-26 00:00:00' AS DATETIME) BETWEEN '2010-07-25' AND '2010-07-26' |
+-------------------------------------------------------------------------------+
|                                                                             1 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

range指定は対象カラムの型に自動変換されるみたい。

DateとDatetimeの比較演算#

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| UTC                | UTC                 |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select CAST('2014-08-04 13:00:00' AS DATETIME) <= '2014-08-04';
+---------------------------------------------------------+
| CAST('2014-08-04 13:00:00' AS DATETIME) <= '2014-08-04' |
+---------------------------------------------------------+
|                                                       0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select CAST('2014-08-05 00:00:00' AS DATETIME) <= '2014-08-04';
+---------------------------------------------------------+
| CAST('2014-08-04 10:00:00' AS DATETIME) <= '2014-08-04' |
+---------------------------------------------------------+
|                                                       0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select CAST('2014-08-04 00:00:00' AS DATETIME) <= '2014-08-04';
+---------------------------------------------------------+
| CAST('2014-08-04 00:00:00' AS DATETIME) <= '2014-08-04' |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select CAST('2014-08-04 00:00:01' AS DATETIME) <= '2014-08-04';
+---------------------------------------------------------+
| CAST('2014-08-04 00:00:01' AS DATETIME) <= '2014-08-04' |
+---------------------------------------------------------+
|                                                       0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)