Graham Anderson <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] >
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
