MySQLの日付データについて

質問

PHPでMySQLから日付データを表示しようとしているのですが、日付フィールドの値をvarcharにしてしまいました。

このような状況下での質問なのですが、varcharフィールドに2006年1月1日と2006年2月1日と2006年11月1日がある場合、PHPで日付データを表示するとこのような順番で表示されてしまいます。

2006年1月1日
2006年11月1日
2006年2月1日

下記のようなカレンダー順の表示にするにはどのような方法があるでしょうか？

2006年1月1日
2006年2月1日
2006年11月1日

また、下記の質問と回答を拝見したのですが、日付データはタイムスタンプで処理するのが一般的なのでしょうか？
http://oshiete1.goo.ne.jp/qa1396724.html
その場合、フィールドタイプをDATEにしておいて、PHPからMySQLに書き込む際はどのような形式が正しいのでしょうか？
2006/1/1のまま書き込んでも自動的にタイムスタンプに変換してくれるのでしょうか。

最後に私のような初心者がMySQLを勉強する際にお勧めの書籍やウェブサイトなどありましたら是非教えて頂けると幸いです。
よろしくお願いします。

回答No.1

chukenkenkou · ベストアンサー

文字で格納する場合は、月日をそれぞれ2桁で表さないと、日付順のソートはできません。

>カレンダー順の表示にするにはどのような方法があるでしょうか？

DB格納時に、'2006年01月01日'と月日を必ず2桁にしておくか、検索時（あるいはその後）に'月'や'日'を文字列サーチして、月日が1桁なら'0'を文字結合するといった方法が考えられます。

>日付データはタイムスタンプで処理するのが一般的なのでしょうか？

「何を管理するか」です。
誕生日などのデータであれば、時刻は必要ありません。
更新履歴などのデータであれば、同じ日に2回以上更新されることがないなら日付だけでもいいだろうし、時刻が必要な場合もあるでしょう。

MySQLのtimestamp型は、特殊なので注意が必要です。多くのRDBMSでは、timestamp型に利用者側が取得した日時を格納できます。しかし、MySQLでは、MySQL側が勝手に値を取得して格納します。利用者側で取得した日時を格納するには、datetime型を使う必要があります。

今回のように日付だけ管理できればいいなら、date型で十分でしょう。

>フィールドタイプをDATEにしておいて、PHPからMySQLに書き込む際はどのような形式が正しいのでしょうか？

文字形式で、'yyyy-mm-dd'といった形式で格納します。

例を示すと、以下のような感じです。

create table t1
(hiduke date);
insert into t1 values('2007-6-1');

参考URL：

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

お礼

timestamp型が一般的というわけでもないんですね。
参考になりました。
ありがとうございました。

回答No.2

taketan_mydns_jp

varcharでは文字列として判断するので、ソートは難しいかもしれませんね。
入力されているデータを一旦全部呼び出して、変換してから再度データベースに書き込むのがいいと思います。
うっかりテーブルが無くなっては困るでしょうから、新しいテーブルに書き込むようにした方が良いですね。
変換は正規表現を使えば簡単だと思います。下記は文字列を数値配列にする例です。
$dstr = "2006年1月1日";
if(preg_match("/^([0-9]+)年([0-9]+)月([0-9]+)日/i",$dstr, $matches)){
print_r($matches);//Array ( [0] => 2006年1月1日 [1] => 2006 [2] => 1 [3] => 1 )
}

> 日付データはタイムスタンプ型が一般的か？
日付データはTIMESTAMP型（YYYYMMDDHHMMSS）で、というのはそれほど一般的では有りません。PHP側で抽出、変換が案外面倒だからです。文字通り、タイムスタンプなので、自動的にログを残す、と言う事に主眼が置かれます。
日付にはDATE型（YYYY-MM-DD）があります。恐らくこれが一番一般的でしょう。その他、時間も追加した、DATETIME型（YYYY-MM-DD HH:MM:SS）もあります。
私がよく使うのは、数値のINT型にしておいて、mktime()等で作ったUNIX TIMEを格納する事です。データベースを変えた時等にも汎用性が有り（日付型はデータベースによって仕様がかなり違う）、しかもPHPからの呼び出しが楽です。
呼び出したデータを簡単にフォーマット出来ますし（例　date("Y-m-d",$data)、date("Y年m月d日 a h 時",$data) ）、呼び出す場合も d >' mktime(0,0,0,$m,$d,$y) ' というように日付を指定して呼び出す事も可能ですので。

参考まで。

お礼

>入力されているデータを一旦全部呼び出して、変換してから再度データベースに書き込むのがいいと思います。

具体的なアドバイスありがとうございます。
参考になりました。
ありがとうございました。