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
==================