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.