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を使用しています。

回答の条件
  • 1人2回まで
  • 登録:2008/02/10 23:39:51
  • 終了:2008/02/11 01:05:41

ベストアンサー

id:bonlife No.2

回答回数421ベストアンサー獲得回数752008/02/11 00:52:44

ポイント100pt

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')
;
id:kt26

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


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

2008/02/11 01:05:11

その他の回答(2件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542008/02/10 23:57:33

ポイント15pt

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

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 日付と時刻関数

id:kt26

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


お店の営業時間など、「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時以降は表示されないのではないでしょうか?


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

2008/02/11 00:26:52
id:bonlife No.2

回答回数421ベストアンサー獲得回数752008/02/11 00:52:44ここでベストアンサー

ポイント100pt

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')
;
id:kt26

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


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

2008/02/11 01:05:11
id:un0 No.3

un0回答回数651ベストアンサー獲得回数322008/02/11 00:53:35

ポイント10pt

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'

のようになります。

参考になれば幸いです。

id:kt26

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

2008/02/11 01:01:47
  • id:kt26
    すみません。質問のSQLの一部が間違っていました。(WHEREが抜けていました。)

    SELECT * FROM shop WHERE start_time<'$date' && last_time>'$date'
  • id:chuken_kenkou
    まず、SQLの訂正から。

    insertとselectの例で、「current_time」としているのは、「current_timestamp」の誤りでした。

    また、「現在時刻に受付している店」を検索したかったのですね。勘違いしていました。

    私もstart_timeよりlast_timeが小さい場合、last_timeに24時間を加算する方法でテスト中でした。

    なお、検索条件でif関数など、列を加工したり条件分岐などを行うと、仮にインデクスがあっても
    使われないので、データ量が多い場合は注意してください。
  • id:kt26
    追記ありがとうございます。インデックスの件、了解しました。参考にさせていただきます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません