View Full Version : Calculating the difference between several times in SQL

03-05-2009, 04:43 PM

Firstly let me explain what I am aiming for. I am creating a javascript based stats tracker that will let me gather information about other sites I own without having to duplicate my code. The code currently works by including a small javascript snippet at the bottom of the page, which runs a PHP script that inputs the data into the database.

The data is stored in the following format:

id- ip address - timestamp - page - unique etc...

I am trying to work out the duration of the visit by the user. I have previously tried a onBeforeUnload function that will send a new request to the script, however because this is an externally loaded script it doesnt get a chance to load as the user navigates away from the page, so therefore no data is sent.

Unless anyone can help with this, then I need help with the following:

I wish to be able to extract the duration of visits via SQL. For example:
Select the start time from 1 IP Address, look for next visit for that IP within xx amount of seconds (for timeouts), calculate the difference in the timestamp, then repeat until there are no more entries within xx amount of seconds for that IP address.

I would to do this within 1 single SQL for efficiency.

However the problem with this method is that I wont get the duration of the time spent on the last visited page, as there is no next row to compare against.

Another solution I was thinking was to have a AJAX request every 20 secs to a script that updates the duration for that visitor, but this is far from efficient in terms of coding and bandwidth etc.

If anyone has any thoughts or can shine some light onto this I will be very very grateful. Also if anyone has managed to do this before on here, and can help that would be awesome!

03-05-2009, 06:03 PM
This may not be very helpful from a coding point of view, but surely the webserver your sites are hosted on has advanced stats that can churn out this information? I am not sure you should put a lot of effort into reinventing the wheel as it where.

03-05-2009, 06:47 PM
Yes, each site does that log files, but the problem is that I will not be able to access them from a remote server. The whole point to my method is so that I can get stats from sites and store the details in one main server.

From a coding point of view this makes perfect sense. Sure, I could install the stats script on each site, but when I make an update I will have to change 10+ versions, rather than my way I will just need to change one. Think of it as a really basic Google Analytics.

Thank you for yours response though.