SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,094
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    daily useage report in 5 min increments, logic needed.

    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.
    What I lack in acuracy I make up for in misteaks

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You may want to direct this to the SQL peeps, you should be able to calculate this directly in your chosen RDBMS.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  5. #5
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,094
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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.
    What I lack in acuracy I make up for in misteaks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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...

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about a user who started 5 hours ago, and has not yet stopped?

  8. #8
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,094
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    What I lack in acuracy I make up for in misteaks

  9. #9
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,094
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    crmalibu, I have to take that into account also because, until they log out the stop time is all 0's
    What I lack in acuracy I make up for in misteaks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •