Mike Johnson wrote:
From: René Fournier [mailto:[EMAIL PROTECTED]
I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date....
$sql = "SELECT COUNT(table.id) FROM table WHERE table.created = NOW()";
$num = mysql_result(mysql_query($sql),0);
The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed.
Thanks in advance.
Try this:
SELECT COUNT(table.id) FROM table
WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE();
Should do what you're looking for. HTH!
It will work, but you don't want to do that. As soon as you run your column through a function, you can no longer use the index on that column to choose rows. In other words, this query results in a full table scan. Mysql will have to execute DATE_FORMAT() on table.created for every single row to make the comparison.
Instead, you should always compare columns to constants, if possible. That's not as bad as it may sound, since functions of constants are constants.
In this case (assuming no rows with created in the future), you should use
SELECT COUNT(table.id) FROM table WHERE table.created >= CURDATE();
Mysql will calculate CURDATE() once, convert it to a DATETIME by adding zeros, then select matching rows using the index on table.created.
Another example: Say you wanted the rows which were created in the last 72 hours. Here are some equivalent conditions:
WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(created) <= 72*60*60
WHERE created + INTERVAL 72 HOUR >= NOW()
WHERE created >= NOW() - INTERVAL 72 HOUR
Only the last one can use an index on created, however.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
