Mysqlのdatetime型とtimestanp型で保存されているデータを年月日だけを指定して任意の日にちのデータを取得する方法

Tweet

Pocket

概要

みなさんこんにちはcandleです。今日はmysqlのdatetime型とtimestanp型におさめられているデータの取得を紹介します。

datetimeやtimestanpは多くの場合下のようなフォーマットでデータが保存されています。

2014-03-23 10:23:41

故に、下のSQLではデータが取得できません。

SELECT * FROM users WHERE created =  ' 2014-03-23'

これの対処の仕方を紹介します。

前提

mysqlが自由に動かせる環境。
MAMPやXAMPがあると便利です。

テストデータベースを用意する

下のSQLを使用して今回も紹介します。下のSQL文を実行してusersテーブルと
３つのレコードを作成してください。

私は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テーブルには３つのレコードがあります。
それぞれ、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側からも日にちを指定してデータを取得できます。