Hi Guys,

I have a table of events with a start and finish datetime.

I am trying to write a php function that will return the next earliest
start time that an event will fit. Will this be something that I can
do just in mysql?

or something much more complex?

anyone have any tips?

On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccbrad@gmail.com> wrote:

>Hi Guys,
>
>I have a table of events with a start and finish datetime.
>
>I am trying to write a php function that will return the next earliest
>start time that an event will fit. Will this be something that I can
>do just in mysql?
>
>or something much more complex?
>
>anyone have any tips?

SELECT * FROM mytable
WHERE start > NOW()
ORDER BY start
LIMIT 1

On Fri, 05 Oct 2007 06:58:22 +0200, subtenante <zzsubtenantezz@gmail.com>
wrote:

> On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccbrad@gmail.com> wrote:
>
>> Hi Guys,
>>
>> I have a table of events with a start and finish datetime.
>>
>> I am trying to write a php function that will return the next earliest
>> start time that an event will fit. Will this be something that I can
>> do just in mysql?
>>
>> or something much more complex?
>>
>> anyone have any tips?
>
> SELECT * FROM mytable
> WHERE start > NOW()
> ORDER BY start
> LIMIT 1

That's for the next event indeed. I gathered the OP meant 'next empty
slot'. In which case these 'slots' should either have a table or the OP
should tell us more about them.
--
Rik Wasmus

On 5 Oct, 09:00, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 05 Oct 2007 06:58:22 +0200, subtenante <zzsubtenant...@gmail.com>
> wrote:
>
>
>
>
>
> > On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccb...@gmail.com> wrote:
>
> >> Hi Guys,
>
> >> I have a table of events with a start and finish datetime.
>
> >> I am trying to write a php function that will return the next earliest
> >> start time that an event will fit. Will this be something that I can
> >> do just in mysql?
>
> >> or something much more complex?
>
> >> anyone have any tips?
>
> > SELECT * FROM mytable
> > WHERE start > NOW()
> > ORDER BY start
> > LIMIT 1
>
> That's for the next event indeed. I gathered the OP meant 'next empty
> slot'. In which case these 'slots' should either have a table or the OP
> should tell us more about them.
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

I think he was quite clear (but maybe I'm reading it wrong). All his
events have a start datetime and an end datetime. When he wants to add
a new event, this will have a duration and he wants to find an
unfilled gap in events that will take this duration without
overlapping.

Captain Paralytic <paul_lautman@yahoo.com> wrote in
<1191573275.035269.153280@k79g2000hse.googlegroups .com>:
> On 5 Oct, 09:00, "Rik Wasmus" <luiheidsgoe...@hotmail.com>
> wrote:
>> > On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain
>> > <mccb...@gmail.com> wrote:
>> >> I have a table of events with a start and finish
>> >> datetime.
>>
>> >> I am trying to write a php function that will return
>> >> the next earliest start time that an event will fit.
>> >> Will this be something that I can do just in mysql?
>>
>> I gathered the OP meant 'next empty slot'. In which case
>> these 'slots' should either have a table or the OP should
>> tell us more about them.

Isn't that denormalisation? Anyway, maintaining a table of
empty time slots for a foreseeable future sounds a bit like
an engineering hell to me.

> I think he was quite clear (but maybe I'm reading it
> wrong). All his events have a start datetime and an end
> datetime. When he wants to add a new event, this will have
> a duration and he wants to find an unfilled gap in events
> that will take this duration without overlapping.

I decided to toy with this a little, using:

CREATE TABLE slot (start datetime NOT NULL,end datetime NOT
NULL);
INSERT INTO slot VALUES
('2007-10-05 14:00','2007-10-05 15:00'),
('2007-10-05 15:30','2007-10-05 17:30'),
('2007-10-06 09:00','2007-10-06 14:00'),
('2007-10-06 15:00','2007-10-06 18:00');

Seems to be fairly easy to do, even with additional
restrictions, but that JOIN would be a monster I suppose,
if there were many more records in the table:

SELECT
s1.end AS new_start,
MIN(s2.start) AS new_end,
TIMEDIFF
(
IF
(
DATEDIFF(MIN(s2.start),s1.end),
TIMESTAMPADD(HOUR,18,DATE(s1.end)),
MIN(s2.start)
),
s1.end
) AS duration
FROM slot AS s1 JOIN slot AS s2 ON s2.start>s1.end
WHERE s1.end>NOW()
GROUP BY s1.end
HAVING TIME_TO_SEC(duration)>=2700
ORDER BY new_start ASC
LIMIT 0,1;

Not the most efficient solution overall (better leave stuff
like that to imperative languages), and has a couple of
deficiencies: doesn't account for the start of the working
hours, and does not allocate time after the last event in
the table.

--
It is rare to find learned men who are clean, do not stink,
and have a sense of humour. -- Liselotte in a letter to
Sophie, 30 Jul 1705

On 5 Oct, 12:02, Pavel Lepin <p.le...@ctncorp.com> wrote:
> Captain Paralytic <paul_laut...@yahoo.com> wrote in
> <1191573275.035269.153...@k79g2000hse.googlegroups .com>:
>
> > On 5 Oct, 09:00, "Rik Wasmus" <luiheidsgoe...@hotmail.com>
> > wrote:
> >> > On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain
> >> > <mccb...@gmail.com> wrote:
> >> >> I have a table of events with a start and finish
> >> >> datetime.
>
> >> >> I am trying to write a php function that will return
> >> >> the next earliest start time that an event will fit.
> >> >> Will this be something that I can do just in mysql?
>
> >> I gathered the OP meant 'next empty slot'. In which case
> >> these 'slots' should either have a table or the OP should
> >> tell us more about them.
>
> Isn't that denormalisation? Anyway, maintaining a table of
> empty time slots for a foreseeable future sounds a bit like
> an engineering hell to me.
>
> > I think he was quite clear (but maybe I'm reading it
> > wrong). All his events have a start datetime and an end
> > datetime. When he wants to add a new event, this will have
> > a duration and he wants to find an unfilled gap in events
> > that will take this duration without overlapping.
>
> I decided to toy with this a little, using:
>
> CREATE TABLE slot (start datetime NOT NULL,end datetime NOT
> NULL);
> INSERT INTO slot VALUES
> ('2007-10-05 14:00','2007-10-05 15:00'),
> ('2007-10-05 15:30','2007-10-05 17:30'),
> ('2007-10-06 09:00','2007-10-06 14:00'),
> ('2007-10-06 15:00','2007-10-06 18:00');
>
> Seems to be fairly easy to do, even with additional
> restrictions, but that JOIN would be a monster I suppose,
> if there were many more records in the table:
>
> SELECT
> s1.end AS new_start,
> MIN(s2.start) AS new_end,
> TIMEDIFF
> (
> IF
> (
> DATEDIFF(MIN(s2.start),s1.end),
> TIMESTAMPADD(HOUR,18,DATE(s1.end)),
> MIN(s2.start)
> ),
> s1.end
> ) AS duration
> FROM slot AS s1 JOIN slot AS s2 ON s2.start>s1.end
> WHERE s1.end>NOW()
> GROUP BY s1.end
> HAVING TIME_TO_SEC(duration)>=2700
> ORDER BY new_start ASC
> LIMIT 0,1;
>
> Not the most efficient solution overall (better leave stuff
> like that to imperative languages), and has a couple of
> deficiencies: doesn't account for the start of the working
> hours, and does not allocate time after the last event in
> the table.
>
> --
> It is rare to find learned men who are clean, do not stink,
> and have a sense of humour. -- Liselotte in a letter to
> Sophie, 30 Jul 1705

You appended this to my post, but I wasn't suggesting that he would be
using pre-defined slots.

Captain Paralytic <paul_lautman@yahoo.com> wrote in
<1191584421.969485.214300@g4g2000hsf.googlegroups. com>:
> On 5 Oct, 12:02, Pavel Lepin <p.le...@ctncorp.com> wrote:
>> Captain Paralytic <paul_laut...@yahoo.com> wrote in
>> <1191573275.035269.153...@k79g2000hse.googlegroups .com>:
>> > On 5 Oct, 09:00, "Rik Wasmus"
>> > <luiheidsgoe...@hotmail.com> wrote:
>> >> I gathered the OP meant 'next empty slot'. In which
>> >> case these 'slots' should either have a table or the
>> >> OP should tell us more about them.
>>
>> Isn't that denormalisation? Anyway, maintaining a table
>> of empty time slots for a foreseeable future sounds a bit
>> like an engineering hell to me.
>
> You appended this to my post, but I wasn't suggesting that
> he would be using pre-defined slots.

And I was responding to Rik Wasmus' suggestion. That's why
my response addressing this point was right under his
words. It's part of what inline replying is about. By the
way, may I most respectfully advise you that with a little
effort it's perfectly possible to post properly quoted
replies even while using Google Groups? Of course, using a
real newsreader and newsserver it's even easier than that.

--
It is rare to find learned men who are clean, do not stink,
and have a sense of humour. -- Liselotte in a letter to
Sophie, 30 Jul 1705

Pavel Lepin wrote:
> Captain Paralytic <paul_lautman@yahoo.com> wrote in
> <1191584421.969485.214300@g4g2000hsf.googlegroups. com>:
>> On 5 Oct, 12:02, Pavel Lepin <p.le...@ctncorp.com> wrote:
>>> Captain Paralytic <paul_laut...@yahoo.com> wrote in
>>> <1191573275.035269.153...@k79g2000hse.googlegroups .com>:
>>>> On 5 Oct, 09:00, "Rik Wasmus"
>>>> <luiheidsgoe...@hotmail.com> wrote:
>>>>> I gathered the OP meant 'next empty slot'. In which
>>>>> case these 'slots' should either have a table or the
>>>>> OP should tell us more about them.
>>> Isn't that denormalisation? Anyway, maintaining a table
>>> of empty time slots for a foreseeable future sounds a bit
>>> like an engineering hell to me.
>> You appended this to my post, but I wasn't suggesting that
>> he would be using pre-defined slots.
>
> And I was responding to Rik Wasmus' suggestion. That's why
> my response addressing this point was right under his
> words. It's part of what inline replying is about. By the
> way, may I most respectfully advise you that with a little
> effort it's perfectly possible to post properly quoted
> replies even while using Google Groups? Of course, using a
> real newsreader and newsserver it's even easier than that.
>

Then you should know enough to reply to the message you're responding
to, not some message further down the chain.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 5 Oct, 13:24, Pavel Lepin <p.le...@ctncorp.com> wrote:
> Captain Paralytic <paul_laut...@yahoo.com> wrote in
> <1191584421.969485.214...@g4g2000hsf.googlegroups. com>:
>
> > On 5 Oct, 12:02, Pavel Lepin <p.le...@ctncorp.com> wrote:
> >> Captain Paralytic <paul_laut...@yahoo.com> wrote in
> >> <1191573275.035269.153...@k79g2000hse.googlegroups .com>:
> >> > On 5 Oct, 09:00, "Rik Wasmus"
> >> > <luiheidsgoe...@hotmail.com> wrote:
> >> >> I gathered the OP meant 'next empty slot'. In which
> >> >> case these 'slots' should either have a table or the
> >> >> OP should tell us more about them.
>
> >> Isn't that denormalisation? Anyway, maintaining a table
> >> of empty time slots for a foreseeable future sounds a bit
> >> like an engineering hell to me.
>
> > You appended this to my post, but I wasn't suggesting that
> > he would be using pre-defined slots.
>
> And I was responding to Rik Wasmus' suggestion. That's why
> my response addressing this point was right under his
> words. It's part of what inline replying is about. By the
> way, may I most respectfully advise you that with a little
> effort it's perfectly possible to post properly quoted
> replies even while using Google Groups? Of course, using a
> real newsreader and newsserver it's even easier than that.
>
> --
> It is rare to find learned men who are clean, do not stink,
> and have a sense of humour. -- Liselotte in a letter to
> Sophie, 30 Jul 1705

I know what inline replying is about, but the continuation of the
post, showing examples of the slots was after my post.

At no point in your post did you make any reference to my response to
Rik. Since all you did was respond to Rik's post, you should have
replied to his post. It's part of what threads are all about!!!

When I am able to, I do use a real newsreader. When I am unable to
make the necessary connections to a newserver, I have to make do with
Google Groups.

Rik Wasmus wrote:
> On Fri, 05 Oct 2007 06:58:22 +0200, subtenante
> <zzsubtenantezz@gmail.com> wrote:
>
>> On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccbrad@gmail.com> wrote:
>>
>>> Hi Guys,
>>>
>>> I have a table of events with a start and finish datetime.
>>>
>>> I am trying to write a php function that will return the next earliest
>>> start time that an event will fit. Will this be something that I can
>>> do just in mysql?
>>>
>>> or something much more complex?
>>>
>>> anyone have any tips?
>>
>> SELECT * FROM mytable
>> WHERE start > NOW()
>> ORDER BY start
>> LIMIT 1
>
> That's for the next event indeed. I gathered the OP meant 'next empty
> slot'. In which case these 'slots' should either have a table or the OP
> should tell us more about them.

I tried something like this several years ago in DB2 for a conference
room reservation system. I wasn't overly successful. I did get it to
work with temporary tables and recursive SQL, but the whole thing was a
huge mess. Even as a SP it was complicated and tough to understand.

I finally just did a simple query to fetch allocated timeslots, ordered
by the time, and had a C function find the first available slot. The
code came out much cleaner and more understandable.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle <jstucklex@attglobal.net> wrote in
<BfOdnZ34jYzHrZvanZ2dnUVZ_hudnZ2d@comcast.com>:
> Pavel Lepin wrote:
>> Captain Paralytic <paul_lautman@yahoo.com> wrote in
>> <1191584421.969485.214300@g4g2000hsf.googlegroups. com>:
>>> On 5 Oct, 12:02, Pavel Lepin <p.le...@ctncorp.com>
>>> wrote:
>>>> Captain Paralytic <paul_laut...@yahoo.com> wrote in
>>>>
<1191573275.035269.153...@k79g2000hse.googlegroups .com>:
>>>>> On 5 Oct, 09:00, "Rik Wasmus"
>>>>> <luiheidsgoe...@hotmail.com> wrote:
>>>>>> I gathered the OP meant 'next empty slot'. In which
>>>>>> case these 'slots' should either have a table or the
>>>>>> OP should tell us more about them.
>
>>>> Isn't that denormalisation? Anyway, maintaining a table
>>>> of empty time slots for a foreseeable future sounds a
>>>> bit like an engineering hell to me.
>
>>> You appended this to my post, but I wasn't suggesting
>>> that he would be using pre-defined slots.
>>
>> And I was responding to Rik Wasmus' suggestion. That's
>> why my response addressing this point was right under his
>> words. It's part of what inline replying is about. By the
>> way, may I most respectfully advise you that with a
>> little effort it's perfectly possible to post properly
>> quoted replies even while using Google Groups? Of course,
>> using a real newsreader and newsserver it's even easier
>> than that.
>
> Then you should know enough to reply to the message you're
> responding to, not some message further down the chain.

Actually, I'm unsure as to what's the accepted norm on
techie newsgroups is. I've seen people doing both, do not
remember any discussions on this issue, and skimming over
netiquette guidelines didn't uncover anything on this as
well. Personally, I've always been gravitating towards
concentrating the discussion in one place (occasionally
even going too far in responding to several posts from
different sub-threads in just one message--without properly
updating the References header) instead of splitting it
into penny-packets, but I might've been entirely wrong in
doing this. Perhaps you're aware of an authoritative source
on the problem?

--
It is rare to find learned men who are clean, do not stink,
and have a sense of humour. -- Liselotte in a letter to
Sophie, 30 Jul 1705

On Fri, 05 Oct 2007 14:58:48 +0200, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

> Rik Wasmus wrote:
>> On Fri, 05 Oct 2007 06:58:22 +0200, subtenante
>> <zzsubtenantezz@gmail.com> wrote:
>>
>>> On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccbrad@gmail.com> wrote:
>>>
>>>> Hi Guys,
>>>>
>>>> I have a table of events with a start and finish datetime.
>>>>
>>>> I am trying to write a php function that will return the next earliest
>>>> start time that an event will fit. Will this be something that I can
>>>> do just in mysql?
>>>>
>>>> or something much more complex?
>>>>
>>>> anyone have any tips?
>>>
>>> SELECT * FROM mytable
>>> WHERE start > NOW()
>>> ORDER BY start
>>> LIMIT 1
>> That's for the next event indeed. I gathered the OP meant 'next empty
>> slot'. In which case these 'slots' should either have a table or the OP
>> should tell us more about them.
>
> I tried something like this several years ago in DB2 for a conference
> room reservation system. I wasn't overly successful. I did get it to
> work with temporary tables and recursive SQL, but the whole thing was a
> huge mess. Even as a SP it was complicated and tough to understand.
>
> I finally just did a simple query to fetch allocated timeslots, ordered
> by the time, and had a C function find the first available slot. The
> code came out much cleaner and more understandable.

Yup, I'd hughly prefer 'or the OP should tell us more about them' portion
of the answer. Aside from 'taken' timeslots, what are his requirements? Do
we take 'working hours' into account for this system or is it continuous,
are 'slots' of an arbitrary length or set/rounded to something, what are
the other requirements for an event to 'fit', etc. etc.

One could device a query getting all upcoming 'holes' in a schedule, plus
a 'slot' of a predefined length after the last ending time in the table,
filter them for length, and find the first slot with a particular minimum
length in the results. How this would look exactly would heavily depend on
the actual needs.
--
Rik Wasmus

On 5 Oct, 14:43, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 05 Oct 2007 14:58:48 +0200, Jerry Stuckle
>
>
>
> <jstuck...@attglobal.net> wrote:
> > Rik Wasmus wrote:
> >> On Fri, 05 Oct 2007 06:58:22 +0200, subtenante
> >> <zzsubtenant...@gmail.com> wrote:
>
> >>> On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccb...@gmail.com> wrote:
>
> >>>> Hi Guys,
>
> >>>> I have a table of events with a start and finish datetime.
>
> >>>> I am trying to write a php function that will return the next earliest
> >>>> start time that an event will fit. Will this be something that I can
> >>>> do just in mysql?
>
> >>>> or something much more complex?
>
> >>>> anyone have any tips?
>
> >>> SELECT * FROM mytable
> >>> WHERE start > NOW()
> >>> ORDER BY start
> >>> LIMIT 1
> >> That's for the next event indeed. I gathered the OP meant 'next empty
> >> slot'. In which case these 'slots' should either have a table or the OP
> >> should tell us more about them.
>
> > I tried something like this several years ago in DB2 for a conference
> > room reservation system. I wasn't overly successful. I did get it to
> > work with temporary tables and recursive SQL, but the whole thing was a
> > huge mess. Even as a SP it was complicated and tough to understand.
>
> > I finally just did a simple query to fetch allocated timeslots, ordered
> > by the time, and had a C function find the first available slot. The
> > code came out much cleaner and more understandable.
>
> Yup, I'd hughly prefer 'or the OP should tell us more about them' portion
> of the answer. Aside from 'taken' timeslots, what are his requirements? Do
> we take 'working hours' into account for this system or is it continuous,
> are 'slots' of an arbitrary length or set/rounded to something, what are
> the other requirements for an event to 'fit', etc. etc.
>
> One could device a query getting all upcoming 'holes' in a schedule, plus
> a 'slot' of a predefined length after the last ending time in the table,
> filter them for length, and find the first slot with a particular minimum
> length in the results. How this would look exactly would heavily depend on
> the actual needs.
> --
> Rik Wasmus

Is there a technical objection to a purely MySQL solution? Otherwise,
wouldn't (a tidier version of) something like this work...?

CREATE TABLE `bookings` (
`booking_id` tinyint(4) NOT NULL auto_increment,
`start` datetime NOT NULL,
`end` datetime NOT NULL,
PRIMARY KEY (`booking_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `bookings` VALUES (6, '2007-10-01 00:00:00', '2007-10-05
00:00:00');
INSERT INTO `bookings` VALUES (8, '2007-10-09 00:00:00', '2007-10-14
00:00:00');
INSERT INTO `bookings` VALUES (10, '2007-10-15 00:00:00', '2007-10-18
00:00:00');
INSERT INTO `bookings` VALUES (12, '2007-10-23 00:00:00', '2007-10-26
00:00:00');
INSERT INTO `bookings` VALUES (14, '2007-11-01 00:00:00', '2007-11-05
00:00:00');


SELECT x.end 'from', y.start 'to', DATEDIFF( y.start, x.end )
my_interval
FROM (

SELECT t1.booking_id booking_id_a, COUNT( t1.booking_id ) AS rank_a,
t1.end
FROM bookings t1
LEFT JOIN bookings t2 ON t1.booking_id >= t2.booking_id
GROUP BY booking_id_a
)x
LEFT JOIN (

SELECT t1.booking_id booking_id_b, COUNT( t1.booking_id ) AS rank_b,
t1.start
FROM bookings t1
LEFT JOIN bookings t2 ON t1.booking_id >= t2.booking_id
GROUP BY booking_id_b
)y ON x.rank_a = y.rank_b -1
HAVING my_interval >=4
ORDER BY my_interval
LIMIT 1 ;

On 6 Oct, 12:01, strawberry <zac.ca...@gmail.com> wrote:
> On 5 Oct, 14:43, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>
>
>
> > On Fri, 05 Oct 2007 14:58:48 +0200, Jerry Stuckle
>
> > <jstuck...@attglobal.net> wrote:
> > > Rik Wasmus wrote:
> > >> On Fri, 05 Oct 2007 06:58:22 +0200, subtenante
> > >> <zzsubtenant...@gmail.com> wrote:
>
> > >>> On Tue, 02 Oct 2007 18:02:58 -0700, ½aßrain <mccb...@gmail.com>wrote:
>
> > >>>> Hi Guys,
>
> > >>>> I have a table of events with a start and finish datetime.
>
> > >>>> I am trying to write a php function that will return the next earliest
> > >>>> start time that an event will fit. Will this be something that I can
> > >>>> do just in mysql?
>
> > >>>> or something much more complex?
>
> > >>>> anyone have any tips?
>
> > >>> SELECT * FROM mytable
> > >>> WHERE start > NOW()
> > >>> ORDER BY start
> > >>> LIMIT 1
> > >> That's for the next event indeed. I gathered the OP meant 'next empty
> > >> slot'. In which case these 'slots' should either have a table or theOP
> > >> should tell us more about them.
>
> > > I tried something like this several years ago in DB2 for a conference
> > > room reservation system. I wasn't overly successful. I did get it to
> > > work with temporary tables and recursive SQL, but the whole thing wasa
> > > huge mess. Even as a SP it was complicated and tough to understand.
>
> > > I finally just did a simple query to fetch allocated timeslots, ordered
> > > by the time, and had a C function find the first available slot. The
> > > code came out much cleaner and more understandable.
>
> > Yup, I'd hughly prefer 'or the OP should tell us more about them' portion
> > of the answer. Aside from 'taken' timeslots, what are his requirements?Do
> > we take 'working hours' into account for this system or is it continuous,
> > are 'slots' of an arbitrary length or set/rounded to something, what are
> > the other requirements for an event to 'fit', etc. etc.
>
> > One could device a query getting all upcoming 'holes' in a schedule, plus
> > a 'slot' of a predefined length after the last ending time in the table,
> > filter them for length, and find the first slot with a particular minimum
> > length in the results. How this would look exactly would heavily dependon
> > the actual needs.
> > --
> > Rik Wasmus
>
> Is there a technical objection to a purely MySQL solution? Otherwise,
> wouldn't (a tidier version of) something like this work...?
>
> CREATE TABLE `bookings` (
> `booking_id` tinyint(4) NOT NULL auto_increment,
> `start` datetime NOT NULL,
> `end` datetime NOT NULL,
> PRIMARY KEY (`booking_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
>
> INSERT INTO `bookings` VALUES (6, '2007-10-01 00:00:00', '2007-10-05
> 00:00:00');
> INSERT INTO `bookings` VALUES (8, '2007-10-09 00:00:00', '2007-10-14
> 00:00:00');
> INSERT INTO `bookings` VALUES (10, '2007-10-15 00:00:00', '2007-10-18
> 00:00:00');
> INSERT INTO `bookings` VALUES (12, '2007-10-23 00:00:00', '2007-10-26
> 00:00:00');
> INSERT INTO `bookings` VALUES (14, '2007-11-01 00:00:00', '2007-11-05
> 00:00:00');
>
> SELECT x.end 'from', y.start 'to', DATEDIFF( y.start, x.end )
> my_interval
> FROM (
>
> SELECT t1.booking_id booking_id_a, COUNT( t1.booking_id ) AS rank_a,
> t1.end
> FROM bookings t1
> LEFT JOIN bookings t2 ON t1.booking_id >= t2.booking_id
> GROUP BY booking_id_a
> )x
> LEFT JOIN (
>
> SELECT t1.booking_id booking_id_b, COUNT( t1.booking_id ) AS rank_b,
> t1.start
> FROM bookings t1
> LEFT JOIN bookings t2 ON t1.booking_id >= t2.booking_id
> GROUP BY booking_id_b
> )y ON x.rank_a = y.rank_b -1
> HAVING my_interval >=4
> ORDER BY my_interval
> LIMIT 1 ;

Hmm, I joined on booking_id, but that was wrong. I guess the join
should be on the start date...

SELECT x.end 'from', y.start 'to', DATEDIFF( y.start, x.end )
my_interval
FROM (

SELECT t1.booking_id booking_id_a, COUNT( t1.booking_id ) AS rank_a,
t1.end
FROM bookings t1
LEFT JOIN bookings t2 ON t1.start >= t2.start
GROUP BY booking_id_a
)x
LEFT JOIN (

SELECT t1.booking_id booking_id_b, COUNT( t1.booking_id ) AS rank_b,
t1.start
FROM bookings t1
LEFT JOIN bookings t2 ON t1.start >= t2.start
GROUP BY booking_id_b
)y ON x.rank_a = y.rank_b -1
HAVING my_interval >=4
ORDER BY my_interval
LIMIT 1 ;