Calculating the difference between several times in SQL
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!