時間帯テーブルから直近空き時間取得

質問

現在の日時を基準に下記テーブルより直近で空いている日付とその時間帯を求めるSQLを教えて下さい。
下記のjikan_tblは、未来の予定のレコードが登録されています。
１レコード目は、2013年9月27日10時から同日の10時15分までの予定のデータです。

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

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

jikan_tbl

date_start int(8),
time_start int(4),
date_end int(8),
time_end int(4)

jikan_tblのレコード
20130927 | 1000 | 20130927 | 1015
20130927 | 1015 | 20130927 | 1030
20130927 | 1030 | 20130927 | 1045
20130927 | 1100 | 20130927 | 1115
20130927 | 1130 | 20130927 | 1140

難しく調べても分かりません。
知恵を貸して下さい。

宜しくお願いします。

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

回答No.2

yambejp · ベストアンサー

datetimeバージョンをざっと

//元データ
create table t_jikan(start datetime,end datetime);
insert into t_jikan values('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');

//後ろ時間が空いているviewをつくっておく
create view v_jikan as
select j1.start,j1.end
from t_jikan as j1
left join t_jikan as j2
on j1.end=j2.start
where j2.start is null;

//空き時間一覧
select v1.end as start,min(v2.start) as end
from v_jikan as v1
inner join v_jikan as v2
on v1.end < v2.start
group by start;

//特定時間の直近の空き時間
select v1.end as start,min(v2.start) as end
from v_jikan as v1
inner join v_jikan as v2
on v1.end < v2.start
where v1.end>='2013-09-27 10:00'
group by start
order by start
limit 1;

ここで'2013-09-27 10:00'を
'2013-09-27 10:45'や'2013-09-27 10:50'や'2013-09-27 11:50'に
変えると状況がヒットするレコードが変わることがわかる

補足

回答ありがとうございます。

実際に実行してみました。
素晴らしいです。
こんな難しいSQLは初めてですが、自分なりに意味を調べて勉強します。

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

回答No.1

yambejp

intをつかっている限り処理が複雑になるのでかなり難しいと思います
startとendをそれぞれdatetimeにしてはいけないの？

補足

回答ありがとうございます。

解決方法があれば
int型でなくdatetimeに変えようと思います。
datetimeにする場合はどのようなSQL文になるのですか？