CODEWITHSUNDEEP
mysql
willdanceforfun
willdanceforfun

Reputation: 11240

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?

Upvotes: 3

Views: 7190

Answers (3)

Phliplip
Phliplip

Reputation: 3632

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.

Upvotes: 11

Thomas Berger
Thomas Berger

Reputation: 1870

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

Upvotes: 0

mikeq
mikeq

Reputation: 815

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

Upvotes: 2

Related Questions