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
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
(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