Count of users who authenticated this month

[kmillecam]

I'm trying to get a count of users who've authenticated to my site in the previous 30 days (this month).

I think I have a query to do the job, but the numbers look too good to be true.

Can you look at my query and see if you see any issues?

mysql> SELECT count(*) FROM users WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= access;
+----------+
| count(*) |
+----------+
|   158551 | 
+----------+
1 row in set (0.72 sec)
 
mysql> SELECT count(*) FROM users;
+----------+
| count(*) |
+----------+
|   167602 | 
+----------+
1 row in set (0.37 sec)
 
mysql> describe users;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name             | varchar(60)      | NO   | UNI |         |                | 
| pass             | varchar(32)      | NO   |     |         |                | 
| mail             | varchar(64)      | YES  | MUL |         |                | 
| mode             | tinyint(1)       | NO   |     | 0       |                | 
| sort             | tinyint(1)       | YES  |     | 0       |                | 
| threshold        | tinyint(1)       | YES  |     | 0       |                | 
| theme            | varchar(255)     | NO   |     |         |                | 
| signature        | varchar(255)     | NO   |     |         |                | 
| created          | int(11)          | NO   | MUL | 0       |                | 
| access           | int(11)          | NO   | MUL | 0       |                | 
| status           | tinyint(4)       | NO   |     | 0       |                | 
| timezone         | varchar(8)       | YES  |     | NULL    |                | 
| language         | varchar(12)      | NO   |     |         |                | 
| picture          | varchar(255)     | NO   |     |         |                | 
| init             | varchar(64)      | YES  |     |         |                | 
| data             | longtext         | YES  |     | NULL    |                | 
| login            | int(11)          | NO   |     | 0       |                | 
| timezone_name    | varchar(50)      | NO   |     |         |                | 
| signature_format | smallint(6)      | NO   |     | 0       |                | 
+------------------+------------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)
 
mysql> select FROM_UNIXTIME(access) from users where uid = 3;
+-----------------------+
| FROM_UNIXTIME(access) |
+-----------------------+
| 2009-11-20 11:36:45   | 
+-----------------------+
1 row in set (0.00 sec)
 
mysql>

[guido2004]

Looks fine to me. You should know if the numbers are right

[kmillecam]

I find it hard to believe that 95% of my registered users have logged into the site in the past 30 days.

[r937]

the numbers are wrong

thanks for providing the table layout, because by itself, the query ~seems~ to be okay

WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= access

however, now consider this valuable clue --

access INT(11)

try this instead --

WHERE UNIX_TIMESTAMP(
      DATE_SUB(CURDATE(),INTERVAL 30 DAY)
           ) <= access

[guido2004]

Ahhh, a date stored in a non-date column...

[kmillecam]

Perfect!
Thanks!

mysql> SELECT count(*)
    -> FROM users
    -> WHERE UNIX_TIMESTAMP(
    -> DATE_SUB(CURDATE(),INTERVAL 30 DAY)
    -> )<= access;
+----------+
| count(*) |
+----------+
|     7940 | 
+----------+
1 row in set (0.02 sec)

mysql>