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>
mysql>
mysql>
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)
Comments