Hello,
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 data.
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 GROUP BY DAYOFWEEK(ts)
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 HOUR(ts)=0
(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 results.
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 hour?
Thanks in advance
Chris
