概要
みなさんこんにちはcandleです。今日はmysqlのdatetime型とtimestanp型におさめられているデータの取得を紹介します。
datetimeやtimestanpは多くの場合下のようなフォーマットでデータが保存されています。
2014-03-23 10:23:41
故に、下のSQLではデータが取得できません。
SELECT * FROM users WHERE created = '2014-03-23'
これの対処の仕方を紹介します。
前提
- mysqlが自由に動かせる環境
テストデータベースを用意する
下のSQLを使用して今回も紹介します。下のSQL文を実行してusersテーブルと3つのレコードを作成してください。
私はbtestデータベースの中に作成します。
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `password` varchar(255) NOT NULL, `age` int(10) NOT NULL, `created` datetime NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; INSERT INTO `users` (`id`, `name`, `password`, `age`, `created`, `modified`) VALUES (1, 'alice', '', 10, '2014-02-14 05:29:13', '2014-03-18 10:23:35'), (2, 'bob', '', 12, '2014-02-17 21:11:44', '2014-02-18 00:40:43'), (3, 'tom', '', 15, '2014-02-18 04:37:07', '2014-02-18 00:41:18');
MAMPの場合はデータベースを選択した後、「SQL」を押します。
テキストエリアのところに、上のSQL文を貼付けて、実行すればusersテーブルが作れます。
作成できました。
usersテーブルのレコードを確認する
usersテーブルには3つのレコードがあります。
それぞれ、createdの値が違います。
今回はaliceのデータをcreatedを照合して取得してみましょう。
年月日だけでデータを取得する
datetime型は年月日以外にも「時」「分」「秒」の値が一緒に入っているので、betweenを使って取得します。
一般式は次の様になります。
SELECT * FROM テーブル名 WHERE (datetime型かtimestanp型のカラム BETWEEN '年月日 00:00:00' AND '年月日 23:59:59')
となります。こうする事で、指定した日のデータが取得できます。
サンプルデータベースをもとに取得してみましょう。
aliceのデータを取得します。
SELECT * FROM users WHERE (created BETWEEN ' 2014-02-14 00:00:00' AND ' 2014-02-14 23:59:59')
下の図を見ても分かる様に、aliceのデータが取得できました。
まとめ
応用技として、数日間のデータも取得したりできます。うまくphpと連帯すれば、php側からも日にちを指定してデータを取得できます。