Help w/ complex mysql query

[Anon]

Hey guys, I have what seems like an easy problem to solve, but for the life of me, I can't come up with a successful (and effecient) query. If anyone could help, I'd appreciate it.

ok, I have a DB table that collects ping info every 5 minutes that looks like this:

DATE TIME src dest latency

2001-12-01 00:00:00 1 2 20
2001-12-01 00:05:00 1 2 21
...
2001-12-03 14:55:00 1 2 19

You get the idea, pretty straightforward. Because of other uses of this info, the date and time had to be separated (this is a HUGE table and we needed to avoid a LIKE statement get query dates for reports). So what I want to do is be able to say "give me all the rows from 2001-12-01 @ 02:00 to 2001-12-05 @ 03:00." This SHOULD return all dates from 12-01@2am - 12-05@3am, but instead it returns from 2am to 3am only for each date. I've tried every way I can think of, with no success (except for concat, which takes FAR too long).

this doesn't work:
SELECT latency from table where src='1' and dest='2' and (date >= '2001-12-01' and time >= '02:00') and (date <= '2001-12-05' and time <= '03:00')

Any ideas?
Thanks,
--Viral

[smarlowe]

Think about what you're asking and you'll come up with the where clause on your own...

you want all entries for the start date from some arbitrary time, along with all the entries for all the days in between it and the start date, regardless of time, along with all the entries for the end date up to a specific time.

So, you need a three way Or-And where clause:

where (date='startdate' AND time>'starttime')
OR
(date>'startdate' AND date<'enddate')
OR
(date='enddate' AND time<'stoptime')

[Anon]

DOH! I knew this would be so simple it would make me look like an idiot! 😉 I had to eliminate dups, but otherwise, that was perfect. Thanks man...

--Viral

[smarlowe]

No problem. It's funny how often the sql logic is pretty much there in english, but you just can't see it.