Average Account Age

2 comments

1. 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.

2. 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.

Comments are closed.