Yesterday I wanted to create a little stats panel for a client that would tell me how many new people had signed up that day. Then I wanted to compare that with Google Analytics unique visitors to come up with a conversion rate for the day.
The Problem
The "creation_date" field in the "users" table was an int(12), and stored as a UNIX timestamp. The MySQL date() functions all work with "real" dates such as 2010-06-18, so comparing the UNIX timestamp with those didn't work.
The Solution
After some careful googling, I combined a couple of solutions to create this:
select id from users where date(from_unixtime(creation_date)) = date(now());
Using the from_unixtime() function, we convert the creation date field into a regular date field in this format: 2010-06-18 00:00:00. Then we run the date() function on that result to strip the 00:00:00 from it.
On the other side, we call the now() function, which gets the current date & time (according to the server, we won't get into timezones here). Then we call the date() function again to strip the 00:00:00 as it returns just the year-mm-dd. Then we compare them, and voila!
I hope this saves you a little searching.
