mysqlにて日付を検索する方法として - PHPプロ！Q&A掲示板

2850

  • 0P

mysqlにて日付を検索する方法として

質問日時 / 2010年8月30日 22:07    回答数 / 17件

Questioner:  syanks  このエントリーをはてなブックマークに追加 

キーワード / php    mysql    日付   

こんばんわ。ご質問させていただきます。


環境としては、PHP5＋MySQLにて稼動しています。

SELECT * FROM テーブル名 
　WHERE カラムA 
　　BETWEEN DATE_FORMAT( DATE_ADD(カラムA, interval -10 day), '%Y-%m-%d' ) AND '$day';

あるカラムに入力されている日付にて検索をかけているのですが、
どうにかうまくデータを抽出してくれません。

例えば、カラムAに
2010-08-30
2010-08-31
2010-09-01
2010-09-02
とデータが入ってたとすると、
2010-08-31が抽出されないなどなど、、

DATE_FORMATにて日付を整理せずに、
date("Y-m-d", strtotime("+10 day"))
にて整理してやるとうまく動くのでデータの値は間違っていないかと思います。

DATE_FORMATにてデータを整理するのはあまりよろしくないのでしょうか？

お教えいただければ幸いです。

この質問への意見の募集は締め切られ、ポイントは既に配分されました。
意見を投稿することはできますが、ポイントを受け取ることはできません。



ツリー一覧

Qmysqlにて日付を検索する方法としてsyanks
┣A01shimixどういう条件で抽出しようとされているんでしょうか？
┣A02pannnabetweenの使い方がおかしいような気がします。 取
┣A03magicflute2m日～n日の範囲を取得したければ、 PHPで、m日とn日
┣A04syanksみなさん、いろいろとご指摘いただきありがとうござい
┃┣A04-1shimix>PHPで行ったほうがいいのか、MySQLにて行ったほうが
┃┃┗A04-1-1syanks>>PHPで行ったほうがいいのか、MySQLにて行ったほうが
┃┃　┗A04-1-1-1shimix並べ替え（order）をphpで書くってどういう意味でしょ
┃┃　　┗A04-1-1-1-1syanks並べ替えをPHPでやるとは、一度MySQLでデータを取得し
┃┃　　　┗A04-1-1-1-1-1shimixあぁ、終了条件（$day）は、まだ9/5じゃないんですね
┃┗A04-2magicflute2>>CODE 例えば、2010年9月1日から10日前の期間、 要
┃　┗A04-2-1syanks>“2010年8月23日”と“カラムA”は同じものでしょうか？
┃　　┣A04-2-1-1pannna>やりたいことを改めてお伝えさせていただくと、 >カ
┃　　┣A04-2-1-2pannnaこの意見は投稿者によって削除されました。
┃　　┣A04-2-1-3magicflute2>>CODE 例えば、2010年9月1日から10日前の期間、 要
┃　　┗A04-2-1-4magicflute2＃要件： ・期限通知期間（カラムAの10日前）～今日
┣A05yuu_a0>>CODE SELECT * FROM テーブル名 　WHERE NOW()
┣A06shimixちょっと誤解されているんじゃないかと思うので補足し
┗A07yuu_a0無駄にレスを増やすつもりは無いけど。（正直いつまで

回答一覧

並び替え：

A01
answerershimix [8月31日 00:34]

どういう条件で抽出しようとされているんでしょうか？

>SELECT * FROM テーブル名 
>　WHERE カラムA 
>　　BETWEEN DATE_FORMAT( DATE_ADD(カラムA, interval -10 day), '%Y-%m-%d' ) AND '$day';

BETWEENの開始日が、当該カラムの10日前というのはBETWEENの意味がないと思いますが・・。

>DATE_FORMATにて日付を整理せずに、
>date("Y-m-d", strtotime("+10 day"))
>にて整理してやるとうまく動くのでデータの値は間違っていないかと思います。

というのとは全然違う内容になっているように見受けられます。

あと、どこまでがphpスクリプトで、SQL文として投げるときにどういう状態なのか・・を整理してください。

この意見に回答する

ツリーへ　TOPへ

A02
answererpannna [8月31日 15:13]

betweenの使い方がおかしいような気がします。

取得したいのが「日付型のカラムのデータの10日前」であれば、select句での指定になるはずです。

「日付Ａから日付Ａの10日前までの日付のデータのレコード」を取得したいのであれば
where句にbetweenで指定することになりますが、指定するのはあくまでも「日付Ａの10日前」であって、
「日付型カラムのカラム名」ではありません。

取得したいデータが本当にそのSQLで取得出来るのかどうか、
もう一度確認してみてはいかがでしょうか？

この意見に回答する

ツリーへ　TOPへ

A03
answerermagicflute2 [8月31日 16:12]

m日～n日の範囲を取得したければ、
PHPで、m日とn日の日付を作ってから、
SQLでBETWEEN等で範囲指定すれば判りやすいと思います。
（恐らくORDER BY, LIMITも必要になるでしょう）

で、'$day'では、変数は展開されない。
文字列連結するか、sprintfを使いましょう。

この意見に回答する

ツリーへ　TOPへ

A04
answerersyanks [9月1日 11:45]

みなさん、いろいろとご指摘いただきありがとうございます。
また、返信遅くなりまして、すみません。

自分で、読み返したところ、ご指摘いただいたSQL文等は、
ミスしておりました。申し訳ないです。

改めて行いたいことを記載させていただきます。

例えば、2010年9月1日から10日前の期間、
要は、2010年8月23日～2010年9月1日までのデータを
抽出したいと思いプログラミングしております。

そして、まず考えたのがBETWEENにて期間を設定して
行う方法です。
BETWEENで行うことは一般的だと思うので
そのまま使って開発を行いたいと思います。（もし一般的でなければご指摘ください）
問題は、日付の計算方法です。
最初は、DATE_FORMATにて行っておりましたが、
どうしてもデータが抜けたりと、求めている結果が出ずにいました、
そこで、PHP側でデータを計算して行ったところ、
思ったとおりの結果が抽出できました。

結果的には、現在思ったとおりの動きをしてくれているので、
いいのですが、今後開発を行っていく上で日付の計算を
PHPで行ったほうがいいのか、MySQLにて行ったほうがいいのか。
迷っている次第です。




再度、DATE_FORMATを使った場合と、PHPを使った場合のコードを
書かせていただきます。
■DATE_FORMAT
$date = date("Y-m-d");

$day = $db->quoteSmart($day);

$sql = "SELECT * FROM テーブル名 
      WHERE カラムA
      BETWEEN DATE_FORMAT( DATE_ADD(カラムA, interval -10 day), '%Y-%m-%d' ) AND '{$day}'";


■PHP
$dataStart = date("Y-m-d", strtotime("-10 day"));
$dateEnd   = date("Y-m-d");

$dayStart = $db->quoteSmart($dayStart);
$dayEnd   = $db->quoteSmart($dayEnd);

$sql = "SELECT * FROM テーブル名 
      WHERE カラムA
      BETWEEN '{$dataStart}' AND '{$dateEnd}'";


長文になってしまいましたが、
お時間ありましたら、よろしくお願いします。

この意見に回答する

ツリーへ　TOPへ

A04-1
replyershimix [9月1日 12:46]

>PHPで行ったほうがいいのか、MySQLにて行ったほうがいいのか。
>迷っている次第です。

phpで書けるのですからphpでセットすべきです。MySQLで行うのはMySQL側でないと書けないものだけにしてください（それがどういうものかわからないようであれば、まだ使う必要はないということです）。

というか

>DATE_FORMAT( DATE_ADD(カラムA, interval -10 day), '%Y-%m-%d' )

現在日時じゃなくて「読み込んだカラムAの日時」の10日前にするのでは意味がないと前のレスでも書きました。今回のケースではデータごとに「違う条件を適用する」意図はありませんよね？

この意見に回答する

ツリーへ　TOPへ

A04-1-1
replyersyanks [9月1日 14:38]

>>PHPで行ったほうがいいのか、MySQLにて行ったほうがいいのか。
>>迷っている次第です。

>phpで書けるのですからphpでセットすべきです。MySQLで行うのはMySQL側でないと書けないものだけ
>にしてください（それがどういうものかわからないようであれば、まだ使う必要はないということです）。

MySQLでないと書けないことだけ書くということが、すみません。よくわからないです。。。
例えば、ソートとかは、PHPでもMySQLでも出来ると思いますが、
スピードや可読性等を考えるとMySQLで行ったほうがいいと思います。違いますでしょうか。
そういう意味じゃないんでしょうか。


>>DATE_FORMAT( DATE_ADD(カラムA, interval -10 day), '%Y-%m-%d' )

>現在日時じゃなくて「読み込んだカラムAの日時」の10日前にするのでは意味がないと
>前のレスでも書きました。今回のケースではデータごとに「違う条件を適用する」意図はありませんよね？ 

すみません。ご指摘の意図を分かっていませんでした。よく考えたら、そうですよね。。。
例えば、9月5日のデータがあった場合、このSQL文だと
8月27日から9月1日の範囲を検索しますよね。そしたら、実際は、10日前なので検出してほしい、
9月5日のデータは、検出されないですね。

ありがとうございました。
貴重なお時間ありがとうございました。

この意見に回答する

ツリーへ　TOPへ

A04-1-1-1
replyershimix [9月1日 15:10]

並べ替え（order）をphpで書くってどういう意味でしょう。全部配列に保持出来る件数という保証はないですからphpでは書けませんよ（処理速度の点からもMySQL側でやるべきことですけどね）。

あくまでも「同じことを」どちらで書くか・・という話です。データの内容から計算しないといけないものはSQL文で列名を使って書きますが、SQL文を組み立てたときに決まっているもの（今回の日付の範囲など）はMySQL側でわざわざ「本日日付」から計算するのは無駄ですよね。php側でもMySQL側でも「本日日付」は同じですから。SQL文を作るときに特定の値で渡せるのですから、MySQL側で計算する必要はありません。

・・ということですけど、理解されました？



（蛇足）
>9月5日のデータは、検出されないですね。

そうですか？BETWEENの開始は8/27（対象日付の10日前）になりますが終了は別の記法（$day）で書いていますから、どんなに古い日付でも$dayまでの全データが抽出されると推測したのですが・・。$dayはphp側の変数ですからSQL文をMySQLに渡すときには確定していますよね？どういう中身になっているかは

>$date = date("Y-m-d");
>
>$day = $db->quoteSmart($day);

この↑記述からは読み取れませんが、quoteSmartでリテラルにしていることはたしかなようですので。

この意見に回答する

ツリーへ　TOPへ

A04-1-1-1-1
replyersyanks [9月2日 09:25]

並べ替えをPHPでやるとは、一度MySQLでデータを取得して、Sort関数で並び替えをする
という意味です。

あと、またミスしてますね。
すみません。完璧なPHPコードが書けなくて、、、

>$date = date("Y-m-d");
>$day = $db->quoteSmart($day);

これですが、
$date = date("Y-m-d");
$day = $db->quoteSmart($date);
ですね。

こちらの記述であれば、9月5日のデータは検出されないですよね？
最初にいただいた、
>BETWEENの開始日が、当該カラムの10日前というのはBETWEENの意味がないと思いますが・・。
このご指摘は、「BETWEEN条件がおかしいですよ。」ということを
おっしゃりたかったのですよね？
当初提示したBETWEEN条件だとこのように9月5日が抜けてしまいますよ？ということですよね？

認識違いましたら、ご指摘いただければと思います。

この意見に回答する

ツリーへ　TOPへ

A04-1-1-1-1-1
replyershimix [9月2日 15:23] (最終編集：9月3日 08:25)

あぁ、終了条件（$day）は、まだ9/5じゃないんですね。私が指摘したのは開始条件のこと（カラムAから計算して、この条件なら「2009/1/1でも抽出される」ということ）です。

というか、最初の条件から変わりすぎです。詳細はmagicflute2さんが[9月2日 13:48]のレスで書かれたことでじゅうぶんだと思うので割愛しますが、最初に↓のように書いていただければよかっただろうと思います。

>カラムAには、期限（〆日）が入っています。その期限10日前から
>アラームをあげたいのです。

であれば（最初の投稿の8/30を基準にすれば）カラムAが2010/8/30～2010/9/9のデータを抜き出すんですよね？違いますか？phpでSQL文を作るときに、BETWEENの開始を当日、終了を当日+10日にして文字列を作るだけでいいハズです。何か大きな勘違いをされてませんか？
  1. $day1 = $db->quoteSmart(date('Y-m-d'));
  2. $day2 = $db->quoteSmart(date('Y-m-d',  strtotime('+10 day')));
  3. $sql = 'select * from tanle_a where col_a between ' . $day1 . ' and ' . $day2;
＃期限を過ぎたデータをどうするかはわかりませんが、仕様に書かれていないので無視します（苦笑



（追記）
蛇足ですが・・

「期限10日前からアラームをあげたい」という要件から「データの期日が今日～10日後のデータを抽出すればいい」の読み替えが出来ないとプログラムは大変です。アルゴリズム・・とまでは行かなくても簡単なロジックを考えずに、要件通りにソースを書こうとすると混乱しますよ。

この意見に回答する

ツリーへ　TOPへ

A04-2
replyermagicflute2 [9月2日 08:39]

  1. 例えば、2010年9月1日から10日前の期間、
  2. 要は、2010年8月23日～2010年9月1日までのデータを
  3. 抽出したいと思いプログラミングしております。
  4. <?php
  5. WHERE カラムA BETWEEN $dataStart AND $dateEnd
このように、やりたい事は明確で、BETWEENの使い方はご存知の様です。

ですが、BETWEENの開始値として、
カラムAを持ってくる事に、疑問は無いのでしょうか？
上記の説明でいう、
“2010年8月23日”と“カラムA”は同じものでしょうか？
  1. WHERE カラムA BETWEEN カラムA AND $day

  1. 今後開発を行っていく上で日付の計算を
  2. PHPで行ったほうがいいのか、MySQLにて行ったほうがいいのか。
  3. 迷っている次第です。
それ以前に、DBに全く違う命令をしているので、
比較できないでしょって話です。

この意見に回答する

ツリーへ　TOPへ

A04-2-1
replyersyanks [9月2日 09:33]

>“2010年8月23日”と“カラムA”は同じものでしょうか？

ん。すみません。意味が良く理解できていません。。。
やりたいことを改めてお伝えさせていただくと、
カラムAには、期限（〆日）が入っています。その期限10日前から
アラームをあげたいのです。

なので、常に”2010年8月23日”は動的である必要があると思っています。


また、
>それ以前に、DBに全く違う命令をしているので、
>比較できないでしょって話です。 
すみません。私の間違えでDBに違う命令を出していますが、
日付計算をDBでやったほうがいいのか、PHPでやったほうがいいのか
を知りたいのです。
DBにまったく違う命令を出していますが、
条件が違うだけで、やりたいことは書かせていただいているので、
この質問内容で、お答えいただくことは難しいでしょうか？

よろしくお願いいたします。

この意見に回答する

ツリーへ　TOPへ

A04-2-1-1
replyerpannna [9月2日 11:13] (最終編集：9月2日 11:27)

>やりたいことを改めてお伝えさせていただくと、
>カラムAには、期限（〆日）が入っています。その期限10日前から
>アラームをあげたいのです。

cronとかで定時実行するプログラムだという前提ですが、
その場合の仕様は、
『(カラムAの日付-10日)の日付≦プログラム実行日≦カラムAの日付であれば
アラームをあげるデータとする』
となるはずですが、where句のSQL構文は仕様を満たす状態になっていますか？

>日付計算をDBでやったほうがいいのか、PHPでやったほうがいいのか
>を知りたいのです。

何を優先するかによって違うでしょう。
実行時間を優先するのであれば、SQLで日付計算した場合のプログラムの実行時間と、
php側で日付計算した場合の実行時間を比べて実行時間が短い方をとればいいだけですし、
ハードウェア要件などに左右されてしまうことを(回答側が)答えることは難しいです。

# インデックスなどがきちんと張られているのであればSQLでやったほうが一般的には早いですけどね
# 実行計画(EXPLAIN)を見ればよく分かると思います

そのあたりを考慮しないで答えるとすれば、SQL側でデータ件数を絞り込むことが出来たほうが
php側でのループ処理の処理回数が減るのでSQLでやったほうがいいとは思いますけどね。

この意見に回答する

ツリーへ　TOPへ

A04-2-1-2
replyerpannna [9月2日 11:13]

この意見は投稿者によって削除されました。

ツリーへ　TOPへ

A04-2-1-3
replyermagicflute2 [9月2日 13:48] (最終編集：9月2日 14:53)

  1. 例えば、2010年9月1日から10日前の期間、
  2. 要は、2010年8月23日～2010年9月1日までのデータを
  3. 抽出したいと思いプログラミングしております。

  1. カラムAには、期限（〆日）が入っています。その期限10日前から
  2. アラームをあげたいのです。
  3. なので、常に”2010年8月23日”は動的である必要があると思っています。

これは別要件ですよね。でも、その件は置いといて、

BETWEENではなく、
期限日の10日前 DATE_ADD(カラムA, INTERVAL '-10' DAY) が、
今日の日付 NOW() 以下に該当したものを抽出する、じゃないですか？
期限をとっくに超えたものをどうするかは未定(この判定はPHPでもいい)ですが。


カラムAに対して、DATE_FORMATはいるのでしょうか。

  1. 日付計算をDBでやったほうがいいのか、PHPでやったほうがいいのか
  2. を知りたいのです。
BETWEEN $dataStart AND $dateEnd の応用でいけると思っておられるから、
どちらがいいかという事になるのでしょう。
上述のとおり、BETWEENは不要です。(MySQLWorkbench上で実際に確認)

カラムAの値全部に対して、10日前を算出する必要があるので、
PHPで日付計算や、期限日10日前かの判定をやるとなると、
SELECT * FROM hoge_tbl; と無条件で全件取得する事になりませんかね。

この意見に回答する

ツリーへ　TOPへ

A04-2-1-4
replyermagicflute2 [9月3日 10:13] (最終編集：9月3日 12:06)

＃要件：
・期限通知期間（カラムAの10日前）～今日の日付 の範囲内を取得。
　また、すでに今日の日付が、カラムAを過ぎてしまった物も取得。
＃一例
今日の日付：2010-09-03
期限日（カラムA）に格納されている日付：2010-08-20～2010-09-15
対象テーブル：会費管理テーブル
  1. // 期限日の各値に対して、逐次、-10DAYを繰り返しながら進める
  2. // 取得する日付は、期限日が-10DAYされたものではなく、格納されている期限日
  3. SELECT * from 会費管理テーブル
  4.  WHERE DATE_ADD(期限日, INTERVAL '-10' DAY) <= NOW()
  5. 　 ORDER BY 期限日 ASC;
--

上記SQL文の結果：2010-08-20～2010-09-13
（その内、2010-08-20～2010-09-02は期限切れ）

以降PHPにて、
期限日内には、アラームを、期限日を過ぎたものには、別処理を。
--
【追記（余談）】
この情報を管理する画面の仕様によって、WHEREの条件式も変わるので、
それにともないPHPとの役割分担も変わるでしょう。
--Case A
■管理画面：有効期限内一覧[期限に余裕あり、アラーム対象]
■管理画面：期限切れ一覧[期限越え]
--Case B
■管理画面：有効期限内一覧[期限に余裕のあり]
■管理画面：アラーム対象一覧[アラーム対象]
■管理画面：期限切れ一覧[期限越え]
--Case C
■管理画面：有効期限内一覧[期限に余裕あり]
■管理画面：期限切れ一覧[アラーム対象、期限越え]
--Case D
■管理画面：＊＊情報一覧[期限に余裕のあり、アラーム対象、期限越え]
etc.

この意見に回答する

ツリーへ　TOPへ

A05
answereryuu_a0 [9月3日 01:55] (最終編集：9月3日 10:40)

  1. SELECT * FROM テーブル名 
  2. 　WHERE NOW()
  3. 　　BETWEEN DATE_ADD(カラムA, INTERVAL -10 DAY) AND カラムA;

又は、

  1. SELECT * FROM テーブル名 
  2. 　WHERE NOW() >= DATE_ADD(カラムA, INTERVAL -10 DAY);

期限切れのものをどうするかは知らない。

> PHP か、MySQL か、
MySQL で完結できるなら、そのほうがいいんじゃない。てか、できる範囲でやればいいんじゃない。

つか、カラム の型は？MySQL のバージョンは？

> BETWEEN DATE_FORMAT( DATE_ADD(カラムA, interval -10 day), '%Y-%m-%d' ) AND '$day'
だと、単なる文字列比較になるけどその辺は？ 0 埋めなら何とか期待通りにはなると思うけど。

DATE_FORMAT は普通出力に使うもんだべ。これこそ、PHP でもできるから、好きなように。

この意見に回答する

ツリーへ　TOPへ

A06
answerershimix [9月3日 08:22] (最終編集：9月3日 08:24)

ちょっと誤解されているんじゃないかと思うので補足しますが「phpで書くかMySQLで書くか」という話は、全部取り出してからphpスクリプトで選択するという意味ではありません（ましてphpスクリプトで並べ替えなどは愚の骨頂です）。抽出条件が今回のようにphpで計算してSQL文にリテラルで書けるようなケースで、SQL文で（DATE_ADDなどを使って）計算させる必要はないということです。

で、レスが増えて埋もれそうなので再掲しますが、下記のように書けばDATE_ADDなんかは要らないわけでMySQLに「取得した列を元に計算させて・・」というのは無駄だということです。

--------   再掲ここから（shimix [9月2日 15:23]のレスより）
>カラムAには、期限（〆日）が入っています。その期限10日前から
>アラームをあげたいのです。

であれば（最初の投稿の8/30を基準にすれば）カラムAが2010/8/30～2010/9/9のデータを抜き出すんですよね？違いますか？phpで SQL文を作るときに、BETWEENの開始を当日、終了を当日+10日にして文字列を作るだけでいいハズです。何か大きな勘違いをされてませんか？

  1. $day1 = $db->quoteSmart(date('Y-m-d'));
  2. $day2 = $db->quoteSmart(date('Y-m-d',  strtotime('+10 day')));
  3. $sql = 'select * from tanle_a where col_a between ' . $day1 . ' and ' . $day2;
--------   再掲ここまで

＃col_aが文字列項目ならこれでいいんだろうけど、日付項目なら記述は
＃どーなんだろうって気はしますけどね。未検証ですのであしからず。

この意見に回答する

ツリーへ　TOPへ

A07
answereryuu_a0 [9月3日 08:43] (最終編集：9月3日 10:49)

無駄にレスを増やすつもりは無いけど。（正直いつまでやってんだって気分だし）

〆日って、カラム A じゃないの。って思ったけど、逆をやってるわけね。
  1. SET @@limit := DATE_ADD(NOW(), INTERVEL +10 DAY); // 毎回計算さすのもアホくさいので、変数にセット。後出し感があるのがむかつく。
  2.  
  3. SELECT * FORM テーブル名
  4.   WHERE カラムA BETWEEN NOW() AND @@limit;

# 無駄な文字列化と、エスケープ処理はするのに、DATE_ADD を惜しむ気持ちがいまいち理解できない。
# 日付を文字列で格納してるなら話は別だけどね。

# ちなみに、PHP と データベース の時間あるいはタイムゾーンがずれてるとなかいよね。
# タイムスタンプなら、タイムゾーンを気にする必要は無い。相対時間ならずれてても平気、は言い過ぎか。まともな処理系なら、サマータイム（日本でも少し前から話題になってるやつ）や閏秒も考慮してくれるでしょ。ついでにタイムスタンプの容量は 4 byte。比較も一回で済む。（2038年問題に対応しても8 byte）。文字列なら 10 byte 最近はそんなせこいこと考えなくていいのか。

この意見に回答する

ツリーへ　TOPへ

<<質問一覧へ



Pick Up Q&A

Q
動的なURLを静的に見せる方法
 このエントリーをはてなブックマークに追加 
A
普通に考えて、mod_rewrite でしょうね。 http://www.nishishi.com/blog/2006/01/mod_rewrite_url.html...

>>続きを読む

GETのままでは検索エンジンのロボットが拾ってくれなかったためにSEO対策として有効だと言われていますね。

▲解説者：岡本（アシアル株式会社 教育コーディネーター兼　システムエンジニア）

Q&A掲示板 新着情報