1

I'm trying to see if there is a way to find an average age of user accounts which came through my wordpress site not including the age of the administrator account.

When I say age I don't mean their actual birth age but the tenure of their account.

Is there a simple way to determine this?

2

WordPress records when a user was registered in the $wpdb->users (usually wp_users) table in the column user_registered. So you can use that to calculate the average account age. There's no internal function for this, so you'll have to use $wpdb directly.

This stackoverflow answer has some good info about calculating the average of a series of dates. Translated to WordPress functions and such:

<?php
function wpse106440_avg_account_age($ignore=1)
{
   global $wpdb;

    return $wpdb->get_col($wpdb->prepare(
        "SELECT DATEDIFF(CURDATE(), MIN(user_registered)) / (COUNT(ID) - 1)"
        . " FROM {$wpdb->users}"
        . " WHERE ID <> %d",
        $ignore
    ));
}

If you're admin user's ID is 1, then...

<?php
$avg_age = wpse106440_avg_account_age(1);

... would be the average account age excluding the admin.

If you, say, want to exclude all administrator level users, you'll have to fetch users via get_users of that role then exclude the array of those ID's (eg NOT IN). WP doesn't have separate tables for roles and caps, making it difficult to query by them in SQL directly -- a users capabilities are stored as a serialized array.

1
  • In my database, the oldest user registered on 2013-03-26 and the newest on 2013-05-31, yet this function gives an average duration of 15.7143. With today being 2013-07-14, the minimum possible value should be 44-- calculated with DATEDIFF. This is returning an average duration of just over 1/3 the age of the shortest actual duration rather than returning something between the shortest (44) and the longest (110) as it should.
    – s_ha_dum
    Jul 14 '13 at 14:40
0

As @chrisguitarguy stated, there is no internal function to do this. Below is my suggested solution. It should ignore any user with the 'administrator' role.

function user_duration_wpse_106440() {
  global $wpdb;
  $admins = get_users(array('role' => 'administrator','fields' => 'ID'));
  $today = date_create();
  $sql = "SELECT user_registered FROM {$wpdb->users}";
  if (!empty($admins)) {
    $sql .= " WHERE ID NOT IN (".implode(',',$admins).")";
  }
  $users = $wpdb->get_col($sql);
  foreach ($users as $k => $v) {
    $age = date_diff($today,date_create($v));
    $users[$k] = $age->days;
  }
  // return on the average as a string
  // return array_sum($users) / count($users);

  // alternate; return both average and total
  return array(
     'average' => (array_sum($users) / count($users)),
     'total'   => count($users)
  );
}
// echo user_duration_wpse_106440();
$user_duration = user_duration_wpse_106440();
echo $user_duration['average'];
echo '<br>';
echo $user_duration['total'];

This gives a sane average when tested on my server. It returns an answer in "days", by the way.

I am toying with some variants. I may post other versions.

8
  • This is interesting, how & where would I put this piece of code? Jul 15 '13 at 1:30
  • Put the function definition in functions.php or a plugin file. Use the function-- last line-- wherever you need it.
    – s_ha_dum
    Jul 15 '13 at 2:04
  • perfect seems to bring up the average right. Is there a way to echo out the qty of how many accounts this calculation is being made? Jul 15 '13 at 18:09
  • You would have to return an array. I can edit the answer. Give me a minute.
    – s_ha_dum
    Jul 15 '13 at 18:10
  • seems like I'm getting a parse error: Parse error: syntax error, unexpected '}' in /home/functions.php on line 174 Jul 15 '13 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.