Help on getting avg time between timestamps of entire table

[TOA]

Hey guys,

 

Here's my scenario: return the avg length of time between logins for all of a companies users combined. (Trying to answer the question "How often do my users login to their accounts"?)

 

Here's my table I'm using:

CREATE TABLE IF NOT EXISTS `IPlog` (
  `IP_ID` int(11) NOT NULL auto_increment,
  `IP` int(10) unsigned NOT NULL,
  `UserName` text collate utf8_unicode_ci NOT NULL,
  `Time_Stamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`IP_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=574 ;

 

When a user logs in, I grab an IP, mark a timestamp and consider that a login. I'm not storing a loggout, because I don't need it to be detailed, just as close a ballpark as I can get. I've considered adding it, and if it's necessary I will but for our uses, it's not that important.

 

I've tried this code which returns a result, but I'm almost 100% sure it's not the right result 

SELECT 
SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, `t1`.`Time_Stamp`, `t2`.`Time_Stamp`))) AS Average 
FROM `IPlog` AS `t1` JOIN `IPlog` AS `t2` ON `t2`.`IP_ID`=`t1`.`IP_ID`+1 
INNER JOIN User ON t1.UserName=User.UserName 
WHERE User.Emplr_ID='1'

 

I tested, and added the IP_ID into the SELECT, and it's only comparing 2 timestamps. Pretty sure it's because of the +1 in there but if I don't have it in it returns 0 because its comparing angainst itself. (my best guess)

 

The forum I found the base of this code used it and prefaced it with:it had to be successive ids, but I can't think of how to switch it.

 

Also, if I change this

INNER JOIN User ON t1.UserName=User.UserName

 

to this

INNER JOIN User ON t2.UserName=User.UserName

 

I get differing results 

11:42:52
11:16:10

 

Any help would be appreciated. I'm not sure if I'm getting the right result or not, and how to get it if I'm not.

 

Thanks

[mikosiko]

I don't see a clear way to solve your request with a straight query...

 

I see 2 options :

- Write a MYSQL stored procedure or function to return the average.

- Load the data in an PHP array and post-process it to get the average.

 

both options should work in a similar way... choose the one that you feel more comfortable with.

[TOA]

I don't see a clear way to solve your request with a straight query...

 

I see 2 options :

- Write a MYSQL stored procedure or function to return the average.

- Load the data in an PHP array and post-process it to get the average.

 

both options should work in a similar way... choose the one that you feel more comfortable with.

 

Hmmm, well thats not what I wanted to hear 

 

I'll look into that post-haste

 

Thank you much sir

[fenway]

Well, yes, if you self-join the table, and ensure increasing UID order with (> in the ON clause), you'll get tuples for each pairing of login times.  But that's not very helpful.

 

You could achieve some magic with user variables, though.