Trying to select the number of mysql rows inserted yesterday

I can normally do this but it appears my brain is not functioning well right now and I'm missing something.

Every day via a cron job that is run at 1am, I want to get a count of rows that were inserted yesterday, and the date.

ie

 SELECT DATE(added) as date, COUNT(*) FROM bookings WHERE added = DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY date

'added' contains a timestamp ie '2011-04-18 12:31:31'

What am I getting wrong here? I know there are many rows added yesterday but my query is returning 0 results and no mysql_开发者_如何学运维errors :(

Any ideas?

Please try

SELECT DATE(added) as yesterday, COUNT(*) FROM bookings WHERE DATE(added) = DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) GROUP BY yesterday

or perhaps

SELECT DATE(added) as yesterday, COUNT(*) FROM bookings WHERE DATE(added) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY yesterday

Updated Corrected the WHERE part.

well whatever NOW() is will return the time portion and unless they were added at exactly that time the day before they wont be counted.

So either use BETWEEN and specify time range, or format the date in your query to only match on the day month year components and not time

WHERE added = does only match exact NOW() - 1 DAY, you should select by a range instead.