File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Using two column functions to return a time span Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using two column functions to return a time span" Watch "Using two column functions to return a time span" New topic
Author

Using two column functions to return a time span

Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

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 ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Using two column functions to return a time span