http://qs1969.pair.com?node_id=209536


in reply to How to do session times The Right Way

I'm confused. Surely this is simply a case of requiring a single line entry per session noting the start and stop times, as indexes within a database, ie:
start_time , stop_time , userid 20020901101111 , 20021002121201 , fred 20020904102010 , NULL , johnwhoneverlogsoff
With such a table, indexed appropriately, the query:
select count(*) from sessions where start_time < $end_of_segment and ( +stop_time > $start_of_segment || stop_time IS NULL);
Should give you the count, and quickly too since the database can make good use of its indexes in that instance.

I would have thought such a table would be a common part of an ISP setup, but if it isn't, you may find the table advantageous for other kinds of session related data as well.

In either case, the only task left is to take your logs and shunt them into a database table of the above format (matching as it goes the appropriate stops with starts, although I am told that since the session time is included in the stop message, you could also just scan stops and calculate the start time from that), something that could be done once per hour, per day, or on demand as necessary. A bit of intelligence in the parser will allow it to continue parsing from where it left off last time, resulting in little CPU burn to get up to date. Hope that helps.