• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using two column functions to return a time span

 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic