SELECT all records that are 30 days old

Question

I need to SELECT all records that are 30 days old. I have the code below but it's not working. In updatestatus I have dates like 12/26/2011. I create a 30 day old date like

$onemonthago="01/01/2012";
$sth = $dbh->prepare(qq(
        SELECT * 
        FROM people 
        WHERE STR_TO_DATE (updatestatus,'%m/%d/%y') 
              <= STR_TO_DATE ( "$onemonthago",'%m/%d/%Y')
                    )  );

Answer 1

If the datatype of updatestatus is date:

SELECT * 
FROM people 
WHERE updatestatus <= '2012-01-01'

or:

SELECT * 
FROM people 
WHERE updatestatus <= CURRENT_DATE() - INTERVAL 1 MONTH

---

If the datatype is datetime or timestamp and you want to check the time part, too:

SELECT * 
FROM people 
WHERE updatestatus <= NOW() - INTERVAL 1 MONTH

You can put an exact datetime instead of the NOW() - INTERVAL 1 MONTH. The correct way depends on how you are storing the datetimes or timestamps (does the Perl code or MySQL creates them in the first place?).

You could also put - INTERVAL 30 DAY which yield slightly different results.

Answer 2

This is what I used. Very simple

$sth = $dbh->prepare(qq(SELECT * FROM people WHERE updatestatus + INTERVAL 30 DAY <=     NOW() )) or die $DBI::errstr;