CODEWITHSUNDEEP
phpmysqltimestampaverage
David Madhatter
David Madhatter

Reputation: 285

AVG datetime interval, show Days and Hours

I'm trying to calculate an average interval between TIMESTAMP (YY-MM-DD HH:MM:SS) records in a column (hit_date) from a table.

I did this in MySql:

SELECT DATEDIFF(MAX(hit_date), MIN(hit_date)) / (COUNT(hit_date) - 1) AS hitavg FROM my_table

This returns a value, ie 135.50.

Then, I did this in PHP to show results:

$value = ($res_from_mysql_query);

$days = (int) $value;
$hours = 24 * ($value - $days);

echo "$days Days, $hours Hours";

and my result is:

135 Days, 0 Hours.

But this is not the correct result... what's wrong? Have I to use TIMEDIFF in MySql? If yes, I got a total different value... so, how can I implement my PHP script?

How can I correctly show Days and Hours for this interval?

Please, help me to improve this, any help would be really appreciated!

Upvotes: 0

Views: 233

Answers (1)

Johnny X. Lemonade
Johnny X. Lemonade

Reputation: 401

what about like that code:

SELECT TIMESTAMPDIFF(HOUR, MIN(hit_date), MAX(hit_date)) / (COUNT(*)-1) AS hitavg FROM my_table

TIMESTAMPDIFF(UNIT, DATETIMEEXP1, DATETIMEEXP2) returns the difference in HOURS between DATETIMEEXP1 and DATETIMEEXP2.. for each "select" the query finds the date of first and last hit (visit) and total visit's count, then calcute arithmetic average. Then in PHP is easy way to display the difference...

Maybe you should look at these MySQL ref:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

Upvotes: 1

Related Questions