posted 20 years ago
I have a table that logs visitors to my website.
They all have unique ids.
They all came in through one channel or another (browse, wap, etc).
Now, I want the 'site visit duration' estimate. So I want the max() minus min() timestamp value for each user.
A couple things:
What about multiple visits separated by multipled days or hours? I could group by the user's session_id instead (which I also have in the table), but I'll miss out on their very first hit (because the server is using URL rewriting until the *next* request).
Does the GROUP BY occur *before* the column functions. I'm thinking yes, but I've been bitten by SQL before that 'looks like it works'.
Also.. is there a way to get this all in one query, rather than what is above, and then programmaticaly subtracting the two dates? If I try select max(hit_timestamp)-min(hit_timestamp) I get '0' for that entire column.
This is on MySql 3.23.42
[ February 10, 2004: Message edited by: Mike Curwen ]