Calculating average time from timestamps

mapg

Hi all!!

I am using php and mysql to make some statistics from my visitors and their accesses.

I would like to know if there is a way with PHP to calculate the access average time between a set of datetime stamps (visitors entries) from Mysql to get a: Average Time Access per User: 1 min, 5 secs.

I have a PHP function to convert seconds to human readable hours, minutes and seconds. That's no problem if work in seconds is a must in this case. The question is how-to calculate the average between all timestamps.

Is it possible?

i.e: I have these values in the Mysql table to extract with PHP ...

Table Name: access

id	ip		country		first_access
1	80.14.97.53	fr		2004-07-27 08:26:38
2	62.34.171.112	fr		2004-07-27 08:30:50
3	213.5.67.240	gr		2004-07-27 08:30:55
4	10.140.209.115	us		2004-07-27 08:31:42
5	217.126.113.147	es		2004-07-27 08:33:24
6	24.157.36.155	ca		2004-07-27 08:35:12
7	218.186.147.156	sg		2004-07-27 08:36:45
8	219.110.41.1	jp		2004-07-27 08:37:19
9	65.54.164.135	us		2004-07-27 08:37:50
10	67.15.35.8	us		2004-07-27 08:43:38
11	195.34.228.140	ru		2004-07-27 08:44:31
12	217.82.81.59	de		2004-07-27 08:55:16

I have done an experiment based in intuition but not with a math logic.

I have summed all UNIX Timestamps (strtotime) elements in the array (array_sum) and divided by the total number of entries to find an arithmetic medium. Later I have experimented with a division (10000000), but certainly it was that .. an experimentation :-(

But certainly I do not entrust that this has a match logic for this case.

Seems a good question for skilled PHP programmers.

Some idea?

$total_entries = @mysql_query("select * from `access`");
$all_entries = @mysql_num_rows($total_entries);

$total_time = @mysql_query("select first_access from `access`");
while ($ttime = @mysql_fetch_array($total_time)) {
	$time_entry = $ttime['first_access'];
	$conversion = strtotime($time_entry);
	$array_insertion[] = $conversion;
}

$total_seconds = array_sum($array_insertion);
$seconds = round(($total_seconds / $all_entries) / 10000000);
echo "Visitors Access Average: " . $seconds;

By this way, I have a something like ...

Visitors Access Average: 109 seconds

But I commented, I am not even sure this is logical.

Thank you very much in advance.

Mapg

Weedpacket

The bit you're missing is the difference between accesses - since you want the duration between accesses.

I've got a bit of code open in another window right now I'll simplify and stick here:

function adjacent_difference($source)
{
	$first = 0;
	$last = count($source);
	$dest = array();
	if($first<$last)
	{
		$prev_value = $source[$first];
		while(++$first<$last)
		{
			$cur_value = $source[$first];
			$dest[] = $cur_value-$prev_value;
			$prev_value = $cur_value;
		}
	}
	return $dest;
}

Given a (sorted) array of (Unix) timestamps, this will return an array of the differences between successive timestamps.

$dest = adjacent_difference(array(1072785661, 1075554122, 1078063383, 1080835444, 1083431105));

will result in $dest being the array(2768461, 2509261, 2772061, 2595661). Take the average of those (keep in mind that there is one less element).

Incidentally, MySQL provides functions for converting its timestamps into Unix-style timestamps - using those will save you a step.

mapg

Thank you very much.

It's works!!!

You are the only one person who reply me about this issue in 5 PHP FORUMS!!

I see that PHP Builder is the most powerful. I will forget the others forever.

Indeed, Thank you again. This math logic was making me crazy.

Mapg

mapg

Follow up a bit this thread I thought that could be interesting to find the average per day, but my first trouble is to extract values from Mysql

Taking this mysql table ...

Table name: access
id	ip		country		first_access
1	80.14.97.53	fr		2004-07-27 08:26:38
2	62.34.171.112	fr		2004-07-27 08:30:50
3	213.5.67.240	gr		2004-07-27 08:30:55
4	10.140.209.115	us		2004-07-28 08:31:42
5	217.126.113.147	es		2004-07-28 08:33:24
6	24.157.36.155	ca		2004-07-28 08:35:12
7	218.186.147.156	sg		2004-07-29 08:36:45
8	219.110.41.1	jp		2004-07-29 08:37:19
9	65.54.164.135	us		2004-07-29 08:37:50
10	67.15.35.8	us		2004-07-30 08:43:38
11	195.34.228.140	ru		2004-07-30 08:44:31
12	217.82.81.59	de		2004-07-30 08:55:16

... and with this PHP code ...

$access_avg_per_day = @mysql_query("select first_access from `access` group by date_format(first_access, '%Y-%m-%d') asc");
	while ($ctime = @mysql_fetch_array($access_avg_per_day)) {
		$time_data[] = $ctime['first_access'];
	}

echo print_r($time_data);

... I just get an array like that ...

Array ( [0] => 2004-07-27 08:26:38 [1] => 2004-07-28 08:31:42 [2] => 2004-07-29 08:36:45 [3] => 2004-07-30 08:43:38 )

Namely the first timestamp of everyday.

I would like to calculate the average time per day but I need all entries for every day, not the first timestamp only.

Any help is really welcome. Thank you in advance.

Mapg