Count rows added today, yesterday...and other times

Question

I was given good direction to solve a problem today from here but I got stuck in trying to follow this direction.

I would like to retrieve a count for all rows from a table for the past 30 days using my date field. I populate these fields using now() and they are in the format Y-m-d h:i:s.

• I need a count for the rows added today, yesterday, 3 days ago...etc
• I guess I can then use the above to get a count for each months?
• Finally a count for the year, year before etc using the months as total?

I was hoping not to specify dates in my query and for the query to just be generic and work out counts relative to today. I was hoping also I could do it accurately too by taking into consideration different months have different number of days.

How can I get this count using just SQL? I then can use the result set as an array and parse this with PHP.

Answer 1

Number of records inserted yesterday:

select count(*) from mytable where date(myfield)=date(date_sub(now(),interval 1 day));

For the year:

select count(*) from mytable where year(myfield)=year(now());

And so on...

Answer 2

Look into MySQLs DATE_ADD and DATE_SUB function it will give you what your looking for.