varjak回答ポイント 70pt

MySQLの、日付型の比較について教えてください。

MySQL:Ver 5.0.21, for pc-linux-gnu (i686) using readline 5.0
OS:Fedora Core 4.0

という環境で、

CREATE TABLE `test_tb` (
`id` int(11) NOT NULL auto_increment,
`i_datetime` datetime NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

というテーブルをつくり、
mysqlプロンプトから次のような検索を行っているのですが、

mysql> SELECT i_datetime FROM test_tb WHERE i_datetime >= '2006-6-1 00:00:00';
+---------------------+
| i_datetime |
+---------------------+
| 2006-06-01 10:14:03 |
| 2006-06-02 15:40:05 |
| 2006-06-04 12:13:54 |
| 2006-06-08 23:04:27 |
| 2006-06-09 01:30:03 |
| 2006-06-10 00:33:36 |
| 2006-06-10 15:28:34 |
| 2006-06-11 16:45:09 |
+---------------------+
8 rows in set (0.00 sec)

mysql> SELECT i_datetime FROM test_tb WHERE DATE(i_datetime) >= '2006-6-1';
Empty set (0.00 sec)

２番目の検索がなぜEmptyになるのか分かりません
どなたか分かる方がいらっしゃったら、教えてください

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ログインして回答する

みんなの回答

llusall2006/06/27 22:26:23
満足35pt

はずしていたら、すみません。

２番目の式

WHERE DATE(i_datetime) >= '2006-6-1';

＃こちらは、DATE(i_datetime)は'2006-06-01'という風にゼロ詰めで比較されていると思います。

＃ですので、emptyになってしまうのではないでしょうか？

　　MySQLリファレンスより

　　DATE(expr)

　　日付式または日付時刻式 expr の日付部分を取り出す。

　　mysql> SELECT DATE('2003-02-09 01:02:03');

　　　　　　　　-> '2003-02-09'

１番目の式で、datetime型と文字列を比較するのは、ちょっと不安です。

次のように、DATE_FORMATで書式設定してから比較してはいかがでしょうか？

WHERE DATE_FORMAT(i_datetime,'%Y-%m-%d') >= '2006-06-01';

スター
- varjak 2006/06/27 23:15:45
  
  回答ありがとうございます！
  
  この比較の場合、文字列として比較されるのでしょうか？
  
  MySQL4.1日本語マニュアルからなんですけど
  
  　　6.3.1.2. 比較演算子
  
  　　引数のどちらかが TIMESTAMP または DATETIME 型のカラムで、もう一方が定数の場合、定数は比較の実行前にタイムスタンプに変換される。これは ODBC との互換性を確保するために行われる。
  
  とあったので、上記の比較式はどちらもタイムスタンプ型として比較されるのを期待していたのですが、
  
  i_datetime >= '2006-6-1 00:00:00'
  
  は「2006-6-1」としてこの結果なので、タイムスタンプ型として、
  
  DATE(i_datetime) >= '2006-6-1'
  
  は文字列型として比較されているような動作が、
  
  どうも腑に落ちないです
  
  追記：
  
  SELECT i_datetime FROM test_tb WHERE DATE_FORMAT(i_datetime,'%Y-%m-%d') >= '2006-06-01';
  
  の結果は、結果は期待通り得ることが出来ました
  
  （i_datetime >= '2006-6-1 00:00:00'の時と同じ）
  
  文字列比較？日付型比較？という仕様については、はっきりしないので、引き続き回答お待ちしております。

凡2006/06/27 22:32:46
満足35pt

http://dev.mysql.com/doc/refman/4.1/ja/date-and-time-types.html

MySQL では、個々の日付または時刻型の値の取り出しは標準形式で行われるが、入力した値（たとえば、日付または時刻型に割り当てる値、またはこれらの型と比較する値の指定時など）については、さまざまな形式で解釈が試みられる。ただし、サポートしている形式は、以降のセクションで説明している形式に限られる。正しい値を指定することが前提となるため、非サポート形式で値を指定すると、予測できない結果が発生する場合がある。

上記はMySQL4.1の説明になりますが、Ver.5でも動作はほぼ同じだと思います。

「さまざまな形式で解釈」に当てはまらないために結果がEmptyになっているだけでしょう。

DATETIMEのデフォルトは'YYYY-MM-DD HH24:MI:SS'という形式になります。

id:varjakさんの例では月日の先頭のゼロが省略されています。

おそらく、MySQL内部でのDATETIME型の解釈において「YYYY-MM-DD HH24:MI:SS」のそれぞれの先頭のゼロが省略されている形でもOKとする、という処理が行われているのでしょう。

また、下記のように前に0をつけると処理結果は秒まで指定した場合と同じになります。

SELECT i_datetime FROM test_tb WHERE DATE(i_datetime) >= '2006-06-01';

おそらく、「YYYY-MM-DDまでしか指定していない場合、時分秒の部分を0として扱う」という処理が行われていることが想像されます。

質問に対する回答ではありませんが、システムを構築する際は、入力値を確実にハンドリングできるように工夫する必要があります。

今回のようなケースでは、DATETIME型の検索条件に使う値はDATETIME型のデフォルトに整形するか、CAST関数を使ってDATETIME型にした上で利用すべきです。

参考になれば幸いです。

スター

varjak 2006/06/27 22:55:20

ありがとうございます、参考にさせていただきます。

「さまざまな形式で解釈」ですが、同じくMySQL4.1用のマニュアルになっちゃいますが、

　　6.2.2.2. DATETIME、DATE、TIMESTAMP 型

　　日付部分の区切り記号を含む文字列として値を指定する場合、10 より少ない月または日の値を 2 桁で指定する必要はありません。'1979-6-9' は '1979-06-09' と同じ意味になります。同様に、時刻部分の区切り記号を含む文字列として値を指定する場合、10 より少ない時、分、または秒の値を 2 桁で指定する必要はありません。'1979-10-30 1:2:3' は '1979-10-30 01:02:03' と同じです。

とあるので、'2006-6-1'は'2006-06-01'と解釈はされると思います。

ただ、

DATE(i_datetime) >= '2006-06-01';

では、期待通り

+---------------------+

| i_datetime |

+---------------------+

| 2006-06-01 10:14:03 |

・

・

と返ってくるのですが、

llusall さんへの返信に記載した内容を踏まえると、

どうもこれは日付型としてではなく、

文字列型として比較された結果そうなったようなきがきます。

比較演算子の仕様上、そうなるのでしょうか・・？

いまいちMySQLの仕様が理解できません

MySQLの、日付型の比較について教えてください。

みんなの回答

llusall2006/06/27 22:26:23
満足35pt

varjak 2006/06/27 23:15:45

凡2006/06/27 22:32:46
満足35pt

varjak 2006/06/27 22:55:20

この質問へのコメント

この質問への反応（ブックマークコメント）

質問の情報

この質問のカテゴリ

この質問に含まれるキーワード

人気の質問

メニュー

PC版

MySQLの、日付型の比較について教えてください。

みんなの回答

llusall2006/06/27 22:26:23 満足35pt

varjak 2006/06/27 23:15:45

凡2006/06/27 22:32:46 満足35pt

varjak 2006/06/27 22:55:20

この質問へのコメント

この質問への反応（ブックマークコメント）

質問の情報

この質問のカテゴリ

この質問に含まれるキーワード

人気の質問

メニュー

PC版

llusall2006/06/27 22:26:23
満足35pt

凡2006/06/27 22:32:46
満足35pt