mySQL date range query

[mickael_8092]

Hello

 

I have the following table of visit information for my web site.

 

-----------------------------------------------------
Id      start                         end
-----------------------------------------------------
a       2008-09-01 15:01        2008-09-01 15:04
b       2008-09-01 15:02        2008-09-01 15:09
c       2008-09-01 15:12        2008-09-01 15:15
d       2008-09-01 16:11        2008-09-01 16:23
e       2008-09-01 16:19        2008-09-01 16:25
f       2008-09-01 17:52        2008-09-01 17:59
g       2008-09-01 18:18        2008-09-01 18:22

 

 

It shows each time some one logs on, and logs off.  The ID is not a userId, just a unique id for the record.

 

I want to be able to query the data so i can create a graph showing concurrent visitors.  Either at 10 min, Hourly, Daily or monthly summarys.

 

For example:

 

2008-09-01 16:10 - 16:20       2
2008-09-01 17:50 - 18:00       1
2008-09-01 18:10 - 18:20       1

 

The above shows me concurrent visitors within ten minute intervals.  The query doesnt have to be that specific, it can just show changes.

 

For example:

 

date                        Count
------------------------------------------
2008-09-01 15:01       1
2008-09-01 15:02       2
2008-09-01 15:04       1
2008-09-01 15:09       0
2008-09-01 15:12       1
2008-09-01 15:15       0
2008-09-01 16:11       1
2008-09-01 16:19       2
2008-09-01 16:23       1
2008-09-01 16:25       0

etc etc etc...

 

 

This result only has an entry in the date column, then the records overlap or finish.  This makes it easy for me to graph the data.

 

I haev been trying this for days now, and so far this is the best i have:

 

SELECT COUNT(id), dateStart
FROM visits a
WHERE 0 < (SELECT COUNT(*)
FROM visits b
WHERE (b.dateStart BETWEEN a.dateStart AND a.dateFinish)
OR
b.dateFinish
BETWEEN a.dateStart AND a.dateFinish)
GROUP BY dateStart;

 

This returns all the records that overlap, so will only show me where i have more than one concurrent visitor.

 

Im totally defeated with this one!!    Any help is *really* appeciated. 

 

 

Many Thanks

Mickael

[fenway]

To do this properly, you need to generate a table on-the-fly of the desired date periods -- one per interval -- and then join & count as necessary.