Tracking Drupal User Registrations by Date10 September 2010

Today I wanted to graph the number of registrations recorded by a Drupal site grouped by date. Drupal stores all account data in the users table. To identify accounts that are registered and verified, I queried with "login != 0" in my WHERE clause, e.g.

SELECT COUNT(*) FROM user WHERE login != 0;

Since Drupal stores all dates as PHP-style Unix timestamps, e.g. 1284157128, I needed to convert those into a form that MySQL understands. I used from_unixtime() to convert the date to a MySQL date type. By casting, I was then able to use the column in a GROUP BY clause, yielding my result:

SELECT COUNT(*), DATE(FROM_UNIXTIME(created)) as created_date
FROM users WHERE login != 0 GROUP BY created_date;

Which produced results just as I wanted them.

Want to talk about this a bit more? Send a tweet to @cgansen or email me at cgansen@gmail.com.