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