人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

PHPとMySQLを使い、「受付時間?終了時間」の検索をテストしています。

次の日にまたぐ時間(10:00?翌2:00など)の場合、どのようにDBに登録して、
どのようなSQL文を書けば、その範囲内にヒットするデータを抽出出来るか分かりません。

日をまたがない場合、以下のようにしていました。
SELECT * FROM shop start_time<'$date' && last_time>'$date'

$date = date("H:i");//現在の時間
start_timeとlast_timeのデータ型は「time」です。

ヒットする:start_timeに「10:00:00」、last_timeに「23:00:00」のレコードがあり、22時にSQLを実行した
ヒットしない:start_timeに「10:00:00」、last_timeに「02:00:00」のレコードがあり、22時にSQLを実行した

出来れば構造や表示(PHP上に表示)は変えたくないので、SQLの書き方で対応出来る方法が
ありましたら、教えていただければと思います。

MySQLは4.1.20を使用しています。


●質問者: kt26
●カテゴリ:ウェブ制作
✍キーワード:00 23:00 dB MySQL PHP
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● chuken_kenkou
●15ポイント

「時間」ではなく、「日時」をデータとして持つようにするのが、一番簡単だと思います。

MySQLには日時のデータ型として、TIMESTAMP型とDATETIME型があります。

TIMESTAMP型は、MySQL側が自動的にINSERT、UPDATE日時を設定してくれます。

というか、されちゃいます。

任意の日時を設定可能にする場合は、DATETIME型にします。



create table shop
(
 start_time datetime,
 last_time datetime
)



INSERTやUPDATE時、CURRENT_TIMESTAMPで日時を自動的に拾えます。また、任意の日時を設定

したい場合は、'2008-02-10 23:50:00'のように、決められた形式で文字定数として指定します。



insert into shop(start_time,last_time) values('2008-02-10 00:00:00',current_time)


検索時の範囲条件は、日時で範囲条件を指定します。


select * from shop
 where start_time>='2008-02-01' and last_time<=current_time

日時の列を作っておけば、そこから日時、日付のみ、時刻のみを得られます。



select
 date(start_time) as shiduke,
 time(start_time) as sjikoku
 from shop


MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.2.2 日付と時刻型

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.4 日付と時刻関数

◎質問者からの返答

日時を持つ場合、「その日を過ぎると表示されない」と言うことはないでしょうか?


お店の営業時間など、「10時?翌2時まで」という場合があります。

しかし、日時を仮に

start_time(2008-02-01 10:00:00)、last_time(2008-02-02 03:00:00)


と登録してしまえば、「2月1日の10時?2月2日の2時まで」しか表示されず、2月3日の3時以降は表示されないのではないでしょうか?


おっしゃる意味や考え方は分かるのですが、ちょっと違うような気がします。(読み違えてたら申し訳ありません)


2 ●
●100ポイント ベストアンサー

last_timeがstart_timeより小さい場合、24時間を足した値を返すようにして、それと比較させてみてはいかがでしょうか。

また、例えば'01:00:00'にSQLを実行することも考慮し、比較対象の時間がstart_timeより小さい場合も、24時間を足しておく必要があります。

上記の考え方を反映したSQLは以下のようになります。

SELECT *
FROM shop
WHERE start_time < IF('$date' < start_time, ADDTIME('$date', CAST('24:00:00' AS TIME)), '$date')
 AND IF(last_time < start_time, ADDTIME(last_time, CAST('24:00:00' AS TIME)), last_time) > IF('$date' < start_time, ADDTIME('$date', CAST('24:00:00' AS TIME)), '$date')
;
◎質問者からの返答

IFを使う考え方は良いですね。こちらのソースでテストしたところ、既存のデータ構造を変えることなく、目的の結果を得ることが出来ました。


凄く勉強になりました。ありがとうございました。


3 ● un0
●10ポイント

TIME型をDATETIME型にするとよいと思います。betweenで範囲指定できます。

(DATETIME 型は、日付と時刻の両方の情報を含む値を必要とするときに使用します。)

受付時間?終了時間

をテーブルの2つのフィールドに持たせているのは、

あるレコードにおいて

受付時間、終了時間から経過時間を算出するためだと推測します。

一方、抽出条件は、

例えば、

受付時間が2008-02-10 10:00:00 から2008-02-11 2:00:00

のようになると思います。

その場合

SQLは

SELECT * FROM shop WHERE 受付時間 between '2008-02-10 10:00:00' and '2008-02-11 2:00:00'

のようになります。

参考になれば幸いです。

◎質問者からの返答

受付日が一定の場合ならそれでいけますが、深夜営業をしているスーパーのような場合、365日営業している場合もあり、日の指定をすると表示されないと思うのです。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ