SQL for time periods

Question

I have statistic table for internet radio (MySQL), there are such columns:

• ip_address
• time_start (datetime of listening start)
• time_end (datetime of listening finish)

I need to select the listeners peak for each day, I mean maximum number of simultaneous unique ip listeners.

And it would be great also to have start and finish time for that peak.

For example:

2011-30-01  |  4 listeners peak  |  from 10:30  |  till 11:25

Answer 1

IMHO it's simpler to load these 35'000 rows in memory, enumerate them, and maintain a count of the concurrent listener at a given moment.
This would be simpler if you load the row in the following format:

IP, Time, flag_That_Indicate_StartOrStop_Listening_For_This_Given_IP

so you'll be able to load the data ordered by time, and the you should simply enumerate all rows maintaining a list of listening IP.

Anyway, how do you consider multiple connections from the same IP?
There can be 10 different listeners behind a NAT using the same IP address.

Update: You don't really need to change the DB structure, it's enough use a different SQL to load the data

SELECT ip_address, Time_Start AS MyTime, 1 As StartStop
FROM MyTable
ORDER BY Time_Start

UNION ALL

SELECT ip_address, Time_Stop AS MyTime, 0 As StartStop
FROM MyTable

Using this SQL you should be able to load all the data, and then enumerate all the rows.
It's important that the rows are sorted correctly.

if StartStop = 1 it's somone that start listening --> Add it's IP to the list of listeners, and increment the listeners count by 1
if StartStop = 0 it's someone that stop listening --> remove it's IP from the list of listeners, and decrement the listeners count by 1

and in the enumeration loop check when you reach the maximum number of concurrent listeners