php - Get from database but only last 30 days

Question

I'm using this php to fetch data from my mysql database:

$wpdb->get_var("select count(*) from " . $wpdb->prefix . "newsletter where status='C'"); ?>

You may notice i'm in wordpress so i'm using the $wpdb variable.

This returns all data from the table where status='C'. This is all working fine but I need to only get the data from the past 30 days. I'm not sure if mysql stores data about when this entry was stored but I do have another column called created in the same row as status. This stores the date in the following format:

2011-10-14 15:33:58

Is there any way to use that to update my code to only retreive the data from the past month?

Any advise here is greatly appreciated.

Thanks C

Answer 1

There is adddate() function in MySQL wich you can use for this :)

$wpdb->get_var("select count(*) from " . $wpdb->prefix . "newsletter where status='C' and date_format(created,'%Y%m%d')>date_format(adddate(now(),interval -30 day),'%Y%m%d')");

This one is for 30 days back but you can replace the "interval -30 day" with "interval -1 month" or visit dev.mysql where you have all explained.

Hope it helps.