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.