Help with mysql query, multiple list

--0016e65386062b08e10469691889
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi All

I am kind of stuck with this query , cant expand my thinking. May this is a
limitation. Here it is

I have a database with many cols two of which are start and end position for
an event.

Now I have a list of event time stamps, I want to find all the info once the
current event time stamp is >= start time of event and <=end time of event.

something like this

select * from table_name where start <= ( LIST of time stamps) AND end >=(
list of time stamps).

Clearly above query accepts only one item in the list. Is there a way to do
this for multiple items in the list ??? I can't think of anything at this
moment.


Thanks,
-Abhi

--0016e65386062b08e10469691889--

Re: Help with mysql query, multiple list

--0016e648d47480c3cb0469693c09
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

aah okie I think I was trying to get too clever. Guess that won't work ...

Thanks,
-Abhi

On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert wrote:

> You'll have to iterate over your two lists of timestamps and build a
> set of ORed conditional pairs:
>
> sql = "select ... from ... where 1 = 0"
> for (i = 0; i < timestamps.length; i++) {
> sql += " or start <=" + timestamps[i] + " and end >= " + timestamps[i]
> }
>
> You'll want to use bind parameters in real life, of course.
>
> cheers,
> barneyb
>
> On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap
> wrote:
> > Hi All
> >
> > I am kind of stuck with this query , cant expand my thinking. May this
> is a
> > limitation. Here it is
> >
> > I have a database with many cols two of which are start and end position
> for
> > an event.
> >
> > Now I have a list of event time stamps, I want to find all the info once
> the
> > current event time stamp is >= start time of event and <=end time of
> event.
> >
> > something like this
> >
> > select * from table_name where start <= ( LIST of time stamps) AND end
> >=(
> > list of time stamps).
> >
> > Clearly above query accepts only one item in the list. Is there a way to
> do
> > this for multiple items in the list ??? I can't think of anything at this
> > moment.
> >
> >
> > Thanks,
> > -Abhi
> >
>
>
>
> --
> Barney Boisvert
> bboisvert@gmail.com
> http://www.barneyb.com/
>

--0016e648d47480c3cb0469693c09--

Re: Help with mysql query, multiple list

--00163616413b27e10b0469693e11
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

why not something like below. Assume you have 3 pairs of start/end
timestamps and you want to find everything within those 3 time periods:

select * from table_name where start >= start1 and end <= end1
union
select * from table_name where start >= start2 and end <= end2
union
select * from table_name where start >= start3 and end <= end3

On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap wrote:

> Hi All
>
> I am kind of stuck with this query , cant expand my thinking. May this is
> a
> limitation. Here it is
>
> I have a database with many cols two of which are start and end position
> for
> an event.
>
> Now I have a list of event time stamps, I want to find all the info once
> the
> current event time stamp is >= start time of event and <=end time of event.
>
> something like this
>
> select * from table_name where start <= ( LIST of time stamps) AND end >=(
> list of time stamps).
>
> Clearly above query accepts only one item in the list. Is there a way to do
> this for multiple items in the list ??? I can't think of anything at this
> moment.
>
>
> Thanks,
> -Abhi
>



--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--00163616413b27e10b0469693e11--

Re: Help with mysql query, multiple list

--0016e6538506c718480469695986
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi Jim

Unfortunately I have thousands of such points. So explicit statement calling
will be very expensive both computationally and in terms of writing..

Thanks,
-Abhi

On Fri, May 8, 2009 at 12:37 PM, Jim Lyons wrote:

> why not something like below. Assume you have 3 pairs of start/end
> timestamps and you want to find everything within those 3 time periods:
>
> select * from table_name where start >= start1 and end <= end1
> union
> select * from table_name where start >= start2 and end <= end2
> union
> select * from table_name where start >= start3 and end <= end3
>
>
> On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap wrote:
>
>> Hi All
>>
>> I am kind of stuck with this query , cant expand my thinking. May this is
>> a
>> limitation. Here it is
>>
>> I have a database with many cols two of which are start and end position
>> for
>> an event.
>>
>> Now I have a list of event time stamps, I want to find all the info once
>> the
>> current event time stamp is >= start time of event and <=end time of
>> event.
>>
>> something like this
>>
>> select * from table_name where start <= ( LIST of time stamps) AND end >=(
>> list of time stamps).
>>
>> Clearly above query accepts only one item in the list. Is there a way to
>> do
>> this for multiple items in the list ??? I can't think of anything at this
>> moment.
>>
>>
>> Thanks,
>> -Abhi
>>
>
>
>
> --
> Jim Lyons
> Web developer / Database administrator
> http://www.weblyons.com
>

--0016e6538506c718480469695986--

Re: Help with mysql query, multiple list

Abhi,

I might not be understanding the problem but could you use the max
and min timestamp values and use something like

SELECT * FROM TABLE WHERE start BETWEEN max AND min AND end BETWEEN max AND min

or

SELECT * FROM TABLE WHERE START IN (1,2,3,4,5) AND END IN(1,2,3,4,5)

I might be completely off-base here though as I don't think I fully
comprehend what you're trying to do.

Kyong

At 09:36 AM 5/8/2009, Abhishek Pratap wrote:
>aah okie I think I was trying to get too clever. Guess that won't work ...
>
>Thanks,
>-Abhi
>
>On Fri, May 8, 2009 at 12:34 PM, Barney Boisvert wrote:
>
> > You'll have to iterate over your two lists of timestamps and build a
> > set of ORed conditional pairs:
> >
> > sql = "select ... from ... where 1 = 0"
> > for (i = 0; i < timestamps.length; i++) {
> > sql += " or start <=" + timestamps[i] + " and end >= " + timestamps[i]
> > }
> >
> > You'll want to use bind parameters in real life, of course.
> >
> > cheers,
> > barneyb
> >
> > On Fri, May 8, 2009 at 9:26 AM, Abhishek Pratap
> > wrote:
> > > Hi All
> > >
> > > I am kind of stuck with this query , cant expand my thinking. May this
> > is a
> > > limitation. Here it is
> > >
> > > I have a database with many cols two of which are start and end position
> > for
> > > an event.
> > >
> > > Now I have a list of event time stamps, I want to find all the info once
> > the
> > > current event time stamp is >= start time of event and <=end time of
> > event.
> > >
> > > something like this
> > >
> > > select * from table_name where start <= ( LIST of time stamps) AND end
> > >=(
> > > list of time stamps).
> > >
> > > Clearly above query accepts only one item in the list. Is there a way to
> > do
> > > this for multiple items in the list ??? I can't think of anything at this
> > > moment.
> > >
> > >
> > > Thanks,
> > > -Abhi
> > >
> >
> >
> >
> > --
> > Barney Boisvert
> > bboisvert@gmail.com
> > http://www.barneyb.com/
> >


Kyong Kim
Instructional Multimedia/Web Programmer
Foothill College
12345 El Monte Rd
3601
Los Altos Hills, CA 94022
650-949-7091


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with mysql query, multiple list

--0016e644cf32d11d2304696972f6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

then either build the statement by way of a program like a perl script or
select all records with a start time after the min start time of all in your
list and an end time less than the max end time in your list then filter
them further either in a program or a store procedure.

On Fri, May 8, 2009 at 11:45 AM, Abhishek Pratap wrote:

> Hi Jim
>
> Unfortunately I have thousands of such points. So explicit statement
> calling will be very expensive both computationally and in terms of
> writing..
>
> Thanks,
> -Abhi
>
>
> On Fri, May 8, 2009 at 12:37 PM, Jim Lyons wrote:
>
>> why not something like below. Assume you have 3 pairs of start/end
>> timestamps and you want to find everything within those 3 time periods:
>>
>> select * from table_name where start >= start1 and end <= end1
>> union
>> select * from table_name where start >= start2 and end <= end2
>> union
>> select * from table_name where start >= start3 and end <= end3
>>
>>
>> On Fri, May 8, 2009 at 11:26 AM, Abhishek Pratap wrote:
>>
>>> Hi All
>>>
>>> I am kind of stuck with this query , cant expand my thinking. May this
>>> is a
>>> limitation. Here it is
>>>
>>> I have a database with many cols two of which are start and end position
>>> for
>>> an event.
>>>
>>> Now I have a list of event time stamps, I want to find all the info once
>>> the
>>> current event time stamp is >= start time of event and <=end time of
>>> event.
>>>
>>> something like this
>>>
>>> select * from table_name where start <= ( LIST of time stamps) AND end
>>> >=(
>>> list of time stamps).
>>>
>>> Clearly above query accepts only one item in the list. Is there a way to
>>> do
>>> this for multiple items in the list ??? I can't think of anything at this
>>> moment.
>>>
>>>
>>> Thanks,
>>> -Abhi
>>>
>>
>>
>>
>> --
>> Jim Lyons
>> Web developer / Database administrator
>> http://www.weblyons.com
>>
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e644cf32d11d2304696972f6--

Re: Help with mysql query, multiple list

abhishek.vit@gmail.com (Abhishek Pratap) writes:

> I am kind of stuck with this query , cant expand my thinking. May this is a
> limitation. Here it is
>
> I have a database with many cols two of which are start and end position for
> an event.
>
> Now I have a list of event time stamps, I want to find all the info once the
> current event time stamp is >= start time of event and <=end time of event.
>
> something like this
>
> select * from table_name where start <= ( LIST of time stamps) AND end >=(
> list of time stamps).
>
> Clearly above query accepts only one item in the list. Is there a way to do
> this for multiple items in the list ??? I can't think of anything at this
> moment.

You said in a later post that you have thousands of events. If they are already
in a table then use that, otherwise put the events into a temporary table
and join the 2 tables together.

Something like this simple example:

mysql> select * from events;
+---------------------+
| event_ts |
+---------------------+
| 2009-05-09 10:29:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from table_name;
+----+---------------------+---------------------+
| id | start_ts | end_ts |
+----+---------------------+---------------------+
| 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 |
| 2 | 2009-05-10 10:00:00 | 2009-05-10 11:00:00 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> select t.* from table_name t, events WHERE event_ts >= start_ts and event_ts <= end_ts;
+----+---------------------+---------------------+
| id | start_ts | end_ts |
+----+---------------------+---------------------+
| 1 | 2009-05-09 10:00:00 | 2009-05-09 11:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

Hope this helps.

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with mysql query, multiple list

What about sub selects. As I see it you only care about the highest
and lowest order of results in each list.

Sorry, in am on a mobile so I can nit make a test case, and this will
be pseudo SQL.

Select * from table where start <= (select foo) and ( select foo) ...

Also look at the "between" and "in" keywords.

Perhaps your list of timestamps is not in a database, can you put then
in?

Either way unless I am reading you wrong you only need the greatest
and lowest time in your comparison, not a huge list.
--
Scott
Iphone says hello.

On May 8, 2009, at 9:26 AM, Abhishek Pratap
wrote:

> Hi All
>
> I am kind of stuck with this query , cant expand my thinking. May
> this is a
> limitation. Here it is
>
> I have a database with many cols two of which are start and end
> position for
> an event.
>
> Now I have a list of event time stamps, I want to find all the info
> once the
> current event time stamp is >= start time of event and <=end time of
> event.
>
> something like this
>
> select * from table_name where start <= ( LIST of time stamps) AND
> end >=(
> list of time stamps).
>
> Clearly above query accepts only one item in the list. Is there a
> way to do
> this for multiple items in the list ??? I can't think of anything at
> this
> moment.
>
>
> Thanks,
> -Abhi

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org