MySQLの日付比較

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

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)

mysql> select '2010-07-26' BETWEEN '2010-07-25' AND '2010-07-26';
+----------------------------------------------------+
| '2010-07-26' BETWEEN '2010-07-25' AND '2010-07-26' |
+----------------------------------------------------+
| 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-04 10: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)

matsubokkuri

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

あわせて読みたい

1件の返信

コメントを残す