I need  a bit if help getting accurate hourly statistics from my web.
I am using: php-mysql
The DB table saves every page hit recording :
id (auto increment), ip, session, timestamp and other non interesting web specific 

I do a summary of hits per weekday using the following query:

SELECT COUNT(DISTINCT(session))as total, DAYOFWEEK(ts) as week_day FROM traffic_daily 

outputing the results as total for each day with no problems.

However I then develop it further to get daily results per hour (still summarising 
overall web stats - not date specific).

For this I am using the following query :

SELECT COUNT(DISTINCT(session))as total FROM traffic_daily WHERE DAYOFWEEK(ts)=1 AND 

(done 24 times for each day of the week ie total of 7 days)

This also works well except that the daily totals don't match up with the previous 
After going over and over my database I realised that the reason for this was obvious 
- some sessions go across hours and are therefore being counted twice.

So, my question is, can anybody suggest any way (probably with php hence the post to 
this list) to only count the hourly sessions that haven't come over from the previous 

Thanks in advance


Reply via email to