4 messages in com.mysql.lists.mysqlRe: Newbie: Average Time on Server qu...
FromSent OnAttachments
Graham Anderson29 Mar 2005 11:20 
SGr...@unimin.com29 Mar 2005 11:50 
SGr...@unimin.com29 Mar 2005 11:58 
SGr...@unimin.com29 Mar 2005 12:02 
Subject:Re: Newbie: Average Time on Server query (typo fix)
From:SGr...@unimin.com (SGr@unimin.com)
Date:03/29/2005 12:02:45 PM
List:com.mysql.lists.mysql

This is it. I quit for today... Change "minutes" to "minute" and I promise to be more careful next time -- ROFLMAO! -S

SGr@unimin.com wrote on 03/29/2005 02:59:09 PM:

TYPO ALERT!!! I left out the all important word INTERVAL. The query should read:

SELECT accesstime FROM userlog where accesstime > (NOW() - INTERVAL 15 minutes) and IP='10.1.7.205' LIMIT 1;

Sorry all!

-S

SGr@unimin.com wrote on 03/29/2005 02:50:35 PM:

Graham Anderson <grah@siren.cc> wrote on 03/29/2005 02:20:48 PM:

I am trying to determine the average time that a Distinct IP address

is

using the server

If I have 15 thousand records of ip addresses and access times like:

IP Now() media.id

-------------------------------------------------------------- 10.1.7.205 20050329121645 67 68.252.32.76 20050329095923 72

And, I want to set the cut-off time to 15 minutes... Basically, if the user has not requested media on the server in last

15

minutes, the user has logged off....

can someone point me in the right direction as I am very new to more

advanced mysql queries

many thanks

-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sgr@unimin.com

Since you didn't post your actual table structure, I will have to make

up

some information (like table and field names). I am also assuming that

you

store your access time in a datetime field and that your server is

v4.1

or

newer.

SELECT accesstime FROM userlog where accesstime > (NOW() - 15 minutes) and IP='10.1.7.205' LIMIT 1;

If you get a record, the user is still active; No record = too late.

Not

only does this use the same clock that you used to create the other entries (the one on the MySQL server) but it avoids the use of MAX() or ORDER BY (both of which will slow you down) and it will use an index if you have one.

If this doesn't work for you, tell us why and we can work towards a solution.

Shawn Green Database Administrator Unimin Corporation - Spruce Pine