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

Question

先程、次のような質問をして、素晴らしい回答をいただきました。
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

gellan · Accepted Answer

場合によっては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]."
";

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

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

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

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

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

gellan · Answer

>ご回答では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分うしろにずらすことができます。

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

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

場合によってはjikan_tblに存在しない日時区分でも選択する必要があるわけですから、

この回答への補足

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

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング