Difference(Datetime) Averages

[jakzodiac]

First off...Hi everyone, I'm hoping you all can help.

 

I'm working on a script that stores datetimes of users logging in and out of a system. I've found out how to get the time the user was logged in using the following code.

 

$q = "SELECT * FROM VISITOR WHERE MONTH(timeIn)=MONTH(NOW()) ORDER BY id";
$monthsVisitors = mysql_query($q);

while($row = mysql_fetch_array($monthsVisitors)) {
 $timeIn = $row['timeIn'];
 $timeOut = $row['timeOut'];
 $timeDiff = dateDiff($timeOut, $timeIn);
}

 

Assuming I store hundreds of entries in the "Visitor" table, with each row containing a "timeIn" and "timeOut". What would be the best way to get an average of "$timeDiff"?

Edited October 21, 2012 by jakzodiac

[Barand]

SELECT AVG(TO_SECONDS(timeOut) - TO_SECONDS(timeIn)) as avTime
FROM visitor

[jakzodiac]

I'm still kind of a noob. How would I implement that properly?

[Barand]

$sql = "SELECT AVG(TO_SECONDS(timeOut) - TO_SECONDS(timeIn)) as avTime
FROM visitor";
$res = mysql_query($sql);
echo "Average = " . mysql_result($res, 0, 'avTime');

[jakzodiac]

Okay, so I tried it out, and I'm getting a blank. Is that compatible with DateTime? I think something is wrong with the query.

Edited October 21, 2012 by jakzodiac

[Barand]

Change TO_SECONDS to UNIX_TIMESTAMP and try that instead

[jakzodiac]

Okay, that worked. It returns the following.

 

1337870.6667

 

When the query returns this value, can it be formatted? I tried to apply different datetime formats and couldn't get any to work. Is this because it is returned as a variable?

 

Lastly, thank you for the help so far. Way more helpful than stackexchange. They kept turning down my questions.

[Barand]

try

SELECT SEC_TO_TIME(AVG(UNIX_TIMESTAMP(timeOut) - UNIX_TIMESTAMP(timeIn))) as avTime
FROM visitor

Edited October 22, 2012 by Barand

[jakzodiac]

Awesome, working out the last of the bugs. Thank you so much Barand!