daily useage report in 5 min increments, logic needed.

[lorenw]

I want to create a report based on 1 hr useage broken down into 5 minute increments to be displayed in a bargraph (like the one that awstats displays).

I have the fields in the database for each user,
starttime 2008-08-17 03:39:39
stoptime 2008-08-17 03:40:14

Can someone recomend a way to accomplish this with out doing 12 queries and calculating the number of users online in 5 minute increments.

How would you handle the person that was online for 2:30 within that 5 min period.

Thanks for any pointers or examples.

[AnthonySterling]

You may want to direct this to the SQL peeps, you should be able to calculate this directly in your chosen RDBMS.

[r937]

the SQL peeps are going to want to know how to relate the start and stop times to the task of reporting data at 5-min intervals, and how to handle the situation where both start and stop fall inside a single interval

[Cups]

Can someone recomend a way to accomplish this with out doing 12 queries and calculating the number of users online in 5 minute increments.

If you want to be able to look at a stat page and it never to be more that 5 minutes old, then no - I don't thing you can.

You'd typically set a cron job off every 5 minutes, and get it to cache the results, but depending on the size of your app, this might get progressively slower as each day wore on.

Looking at your sig, if its a local file for a local station, then shouldn't be a problem I would have thought.

[lorenw]

Yes, it's all in my sig. we will have 400 routers (overseas) with many users on each router. Users can log in and out hence the start and stop time.

We need to get a count of users online for any hour that is broken down into 5 minute segments. this will be fed into jpgraph. Cron would make a huge table I would think.

this is just for testing and thinking along these lines.

SELECT COUNT(RadAcctId) AS useage FROM radacct  
WHERE AcctStartTime >= '2008-08-17 03:30' AND AcctStopTime >= '2008-08-17 03:35'

(Thanks r937, no mysql_numrows)

I can put this into a function and call it 12 times but something just doesn't sound right with this method.

Thanks for the suggestions.

[r937]

so that sort of answers my question -- i think

you have starttime occuring after 3:30, so this suggests that you want to count the number of starting times in each 5 minute interval

what if, for the example you gave, the end time was at 3:47 -- would you count this particular usage in the 3:35-3:40 interval? in the 3:45-3:50 interval?

you see, this is what i was getting at with my earlier question, perhaps i did not ask it the right way...

[crmalibu]

What about a user who started 5 hours ago, and has not yet stopped?

[lorenw]

Thanks r937, This was my question in my first post
"How would you handle the person that was online for 2:30 within that 5 min period."

I think it could work either way as there should be fairly large numbers of users and one or two not reporting for that duration would be fine. (This is an easy way to say "I don't know.)

Most importantly is the logic, I can refine the query later but is it a good idea to put the query into a function and call it 12 times to get each if the 5 min increments.

basically run a "for" loop 12 times that increments the time by 5 miutes and calls the function. Then add the results to an array and send it off to jpgraph.

The more I think of this, it seems to be the only solution, ultimatly I will make this one function do hourly and daily reports also once I get the logic down.

Thanks, I appreciate the input.

[lorenw]

crmalibu, I have to take that into account also because, until they log out the stop time is all 0's