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')
                    )  );

Solution 1

This is what I used. Very simple

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

Solution 2

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.

Solution 3

You can try this way. In SQL, there is dateadd function and I think there should be similar function in MySQL.

select *
from Table
where str_to_date between dateadd(day,-30,getdate()) and getdate()

It retrieve records between current date and past 30 days. You need to adjust for time. If you don't count time, you need to remove timestamp.

Solution 4

If the time column is in timestamp then use below query.(use from_unixtime function)

SELECT wd.* FROM `watchdog` as wd
WHERE  from_unixtime(wd.timestamp) <= NOW() - INTERVAL 1 MONTH

View more solutions

Comments

[t a]

umm.. not 1-30 days back but 30 days plus. There shouldn't be any records more than 30 days old but just in case :)

[t a]

I get this error 'FUNCTION edit.CUR_DATE does not exist' Apparently mysql doesn't recognize this function. What did you mean.. 'If the datatype of updatestatus is date:'??? I have dates like 12/26/2011

[Thit Lwin Oo]

What I meant was..my code.. is finding records btween 30 days past (including time) which mean.. between will check not only date but also time. For example, u run today feb-2, 9am, then will retrieve all records between 2012-01-03 09:00:00.000 and 2012-02-02 09:00:00.000

[ypercubeᵀᴹ]

@ta: Corrected. It's CURDATE() or CURRENT_DATE()

[sigi]

Don't do this. Adding the interval to the field portion disables an existing index on it. MySQL will/have to calculate the interval for every entry.