時間帯テーブルから直近空き時間取得（再質問）

質問

先程、次のような質問をして、素晴らしい回答をいただきました。
http://questionbox.jp.msn.com/qa8279886.html

しかし、ベストな回答でない事に気付きましたので、再び質問させていただきます。
ちなみに、データベースはMYSQLです。

下記が先程の質問の回答を踏まえて少し質問を変えたものです。
重要なのが☆の部分です。

************************************************************************
現在の日時を基準に下記テーブルより直近で空いている日付とその時間帯を求めるSQLを教えて下さい。

下記のjikan_tblは、未来の予定のレコードが登録されています。
１レコード目は、2013年9月27日10時から同日の10時15分までの予定のデータです。

☆（先程の質問はこちら）
15分間隔で予定のレコードを登録していますが、現在の日時が2013年9月27日の10時の場合、
直近で予定を入れられる時間帯を知りたいのでSQLで同日の10時45分から11時が空いていると取得できればベストです。

ちなみに同日の11時15分から11時30分と取得されてしまうのは、NGです。

☆（今回の質問ではこちら）
15分間隔で予定のレコードを登録していますが、直近で予定を入れられる15分間隔の時間帯を知りたいので
次のそれぞれのパターンで取得できればベストです。

現在日時：2013年9月27日の8時50分の場合　→　取得時間帯は9:00～9:15
現在日時：2013年9月27日の9時05分の場合　→　取得時間帯は9:15～9:30
現在日時：2013年9月27日の10時00分の場合　→　取得時間帯は10:45～11:00

現時点から直近の15分区切りの時点を求め、求めた時点から直近で空いている15分間の時間帯を求めたいです。
先程の回答では、27日10時現在では、素晴らしい結果がでましたが、現在時間を27日9時と設定しても
10時45分から11時が求められました。

私の質問の書き方が悪かったと思います。
回答していただいた方にお詫びします。

jikan_tbl

start datetime,
end datetime

jikan_tblのレコード
2013-09-27 10:00| 2013-09-27 10:15
2013-09-27 10:15| 2013-09-27 10:30
2013-09-27 10:30| 2013-09-27 10:45
2013-09-27 11:00| 2013-09-27 11:15
2013-09-27 11:30| 2013-09-27 11:40

知恵を貸して下さい。

宜しくお願いします。

os: windows 7
eclipse: Version: 4.2.0
Build SDK: Android 4.3(API 18)
PHP 5

回答No.1

gellan · ベストアンサー

場合によってはjikan_tblに存在しない日時区分でも選択する必要があるわけですから、
1）1年間の全日時区分を持ったテーブル（all_datetime_tbl）を用意する
2）プログラムのループ構文を使って、必要な日時区分をクエリに作らせる
のどちらかが必要だと思います。

1）
次のようなテーブル（all_datetime_tbl）があれば、答えは簡単に求められます。

each_datetime
-------------------------
2013-01-01 00:00
2013-01-01 00:15
2013-01-01 00:30
（中略）
2013-12-31 23:30
2013-12-31 23:45
-------------------------

select
each_datetime as start_dt, substring(date_add(concat(each_datetime, ':00'), interval 15 minute), 12, 5) as end_t
from
all_datetime_tbl
left join
jikan_tbl on each_datetime = start_datetime
where
concat(each_datetime, ':00') > now() and start_datetime is null
order by
start_dt asc
limit
1（空き候補を直近から5つ表示させるために、5などという設定も私は好きですが）

ただし、all_datetime_tblは随時データを追加していく必要があり、データの追加状況によっては、答えはあるのに結果が表示されないという問題も発生します。
また、1年分を追加すると約35000件（96区分×365（366）日）増加するので、パフォーマンス次第ですが、不要になったレコードは削除していく等のメンテナンスも必要になるかもしれません。

2）
常にテーブルを気にかけるなんて真っ平ゴメンだとおっしゃるなら、必要な日時区分をその都度クエリに作らせましょうか。
プログラムのループ構文を使って、“日時区分を一つ作ってはjikan_tbl内のデータと照合”を繰り返す流れになります。
申し訳ないのですが、私はPerlしか書けないので、後はバイリンガルな方にPHPに翻訳してもらって下さい。

###----------------------ここから------------------------->>>

#---空き候補を直近からいくつ表示させるか
my $cand = 1;

my ($n, $result) = (0, 0);
while ($result < $cand){
$n++;

my $sth = $dbh -> prepare ("
select
substring(each_dt, 1, 16) as start_dt, substring(date_add(each_dt, interval 15 minute), 12, 5) as end_t
from (
select
date_add(concat(date(now()), \' \', hour(now()), \':00:00\'), interval (truncate(minute(now()) / 15 + $n, 0) * 15) minute) as each_dt
) as T1
left join (
select
concat(start_datetime, \':00\') as ref_dt
from
jikan_tbl
) as T2 on each_dt = ref_dt
where
ref_dt is null
");

$sth -> execute();
my $count = $sth -> rows();#---該当件数の取得（0件か1件のどちらか）

if ($count == 1){
my @array = $sth -> fetchrow_array();
print $array[0].' ～ '.$array[1]."\n";

$result++;
}
$sth -> finish();
}

###----------------------ここまで-------------------------///

私の環境（MySQL＋Perl）では動作確認済みです。

こちらの方法に難点があるとすれば、jikan_tbl内のデータが増えれば増えるほど、パフォーマンスもどんどん低下していく可能性があるという点でしょうか。
プログラムのループ処理速度に大きく左右されるため、SQLの速度的恩恵は少ないかもしれません。

最後に、余計なお世話かもしれませんが、素朴な疑問です。
現時刻がたとえ10:44:59でも、これを使えば、直近の候補が10:45と出る可能性は充分あります。
本当にそれで現実的な対応ができるのでしょうか？

お礼

わかりやすい説明でした。

希望通りに動きました。

ありがとうございました。

補足

ご回答ありがとうございます。
動作確認までしていただけるなんて嬉しいです。

perlをphpに翻訳できる人間は周りにいないので、1の案を試してみます。

1の案について質問です。
ご回答では15分区切りのselect文ですが、30分区切りで予定を入れられるように
変更したい場合、all_datetime_tblのデータを全件30分毎に作成し、select文の
interval 15 minuteの部分をinterval 30 minuteに変えるだけでよいのでしょうか？

気にしていただいた件ですが、私も悩みました。
結論は、直近の空き時間を確認するタイミングと実際に空き時間に予定を入れるタイミングは別だと考えました。
直近の確認の時点で10:44:59で、予定を入れるタイミングで10:45:01であれば、予定を入れるタイミングで実際に10:45～11:00に空きがあるか確認しますが、予定は入れられないと判定します。

このように確認時点から少し時間が経過すると10:45～11:00の予定が入れられないことになりますが、対処方法が分からないのでこの対応にしました。

補足に質問してごめんなさい。

回答No.2

gellan

>ご回答では15分区切りのselect文ですが、30分区切りで予定を入れられるように変更したい場合、all_datetime_tblのデータを全件30分毎に作成し、select文のinterval 15 minuteの部分をinterval 30 minuteに変えるだけでよいのでしょうか？

その通りです。
あとjikan_tblのデータも30分間隔に変更してくださいね。

>このように確認時点から少し時間が経過すると10:45～11:00の予定が入れられないことになりますが、対処方法が分からないのでこの対応にしました。

もし、その時間的なずれを、この機能に盛り込んでしまいたいというのであれば、
--------------------------
where
concat(each_datetime, ':00') > now() and start_datetime is null
--------------------------
を
--------------------------
where
concat(each_datetime, ':00') > date_add(now(), interval 1 minute) and start_datetime is null
--------------------------
とすれば、基準となる時刻を1分うしろにずらすことができます。

別にネガティブな話をしたのではなくて、現実的な対応を考えると、基準となる時刻（確認時刻）が現時刻ジャストでは無理があるんじゃないですか？という意味だったのです。

補足

また回答していただき嬉しく思います。

おっしゃる通り現時刻ジャストはできれば避けたかったので助かります。
これからいただいたアドバイス通りに動かしてみますが、結果がとても楽しみです。

分かりやすい説明でした。
ありがとうございました。