MySQLのdatetime型に0月0日を格納

Question

MySQLのdatetime型カラムに「月」や「日」だけ不明なデータを格納したいのですが、どうすれば良いでしょうか？
・具体的なパターンは、下記3フォーマットの何れかです
・2013-03-02
・2013-03
・2013
※月日が不明なデータは全体の一部

■案1
・不明な日付を0に置き換え格納
・2013-03-00
・2013-00-00

■案2
・不明な日付を1に置き換え格納
・2013-03-01
・2013-01-01
※本来の日付と見分けがつかなくなる

■質問
・日付不明が後で判別できるよう、出来れば0で置き換えたい
・MySQL的に問題ないでしょうか？
・一般的に日付が不明なデータはどのように処理するのでしょうか？

■環境
・MySQL
・PHP

mpro-gram · Accepted Answer

一応、0月0日 は許容されています。 年は４桁指定で 2014-00-00 , 2013-03-00 のようになるべく省略せず指定すべきではありますが。マニュアルみるとinsert時の年月日指定フォーマットは、結構柔軟に対処されます。
where 文で比較するときは、そのような日付は、2014-00-00　であれば2013前年より後で、2014-01-01より前と解釈されます。
http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-types.html

「SQL モード」でも許容されるかが変わるので、ご注意ください

参考URL：http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-types.html

mitoneko · Answer

datetime型では、完全な日付時刻しか扱うことが出来ないです。
　残念ですが、案１を実験してみれば、エラーになったと思います。
　日付しか扱わないのであれば・・・
　年・月・日の３つのintフィールドを準備して、不明の部分には、nullを入れるくらいしか思いつけないですね。
　この場合、日付計算に関わる部分は、ＰＨＰ側で処理をするしか有りません。

ｓｑｌ側でも日付処理が必要な何かがあるのであれば、年月日フィールドとは別に、datetimeフィールドを持ち、不明の月や日にはデフォルトで１を適用するくらいかなぁ。この場合は、不明な１か明らかな１かは、月・日のフィールドから判定可能です。
　このフィールドを持つと、情報が冗長化しますので、年・月・日の各フィールドとdatetimeフィールドの整合性は、ｐｈｐ側で保証してあげる必要があります。

文字列で、不明フィールドを０にした日付を持たせるのも一案かと思います。案１の形式で、datetime型を使わずに、char型にしてしまうという手です。
　この場合も、日付計算は、ＰＨＰ側でやらないとしかたがないですね。まともに、ｓｑｌで型変換するエラーになってしまいますから。

freetaka · Answer

MySQL + PHP　という環境で月日が不明な場合がある
というのであれば
日付の取得や計算はPHPのみでやってしまい
MySQLのカラムは文字列で設定し
日付を文字列格納するというのも一案としてありだと思います

MySQLのdatetime型に0月0日を格納

一応、0月0日 は許容されています。

datetime型では、完全な日付時刻しか扱うことが出来ないです。

MySQL + PHP という環境で月日が不明な場合がある

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

一応、0月0日は許容されています。

　datetime型では、完全な日付時刻しか扱うことが出来ないです。

MySQL + PHP　という環境で月日が不明な場合がある