Win a copy of The Way of the Web Tester: A Beginner's Guide to Automating Tests this week in the Testing forum!
  • 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