This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using two column functions to return a time span
 
Similar Threads
an interesting arithmetic, need help.
Project Euler problems with Scala
getting the time in milliseconds
Problem moving columns and rows in 2D array.
Which version to begin?