Date older than xx days

[_matrix_]

Hey,

Trying to grab database records are older than 14 days.. In the database its stored as date/time.

I am wanting to check only on the date part of the field, so when the cron job runs through daily it only grabs records that are 14 days older than today.

I tried a few methods like:

WHERE DATE(timeslot) = DATE(NOW(), INTERVAL 14 DAY)

Which generates an error?

Thanks

[r937]

timeslots 14 days ago --

WHERE timeslot >= CURRENT_DATE - INTERVAL 14 DAY
  AND timeslot  < CURRENT_DATE - INTERVAL 13 DAY

timeslots 14 days and older --

WHERE timeslot < CURRENT_DATE - INTERVAL 14 DAY

[_matrix_]

Thanks.. I did have something like that to start.. but started thinking that I could do it using equal to apposed to where/and..

[_matrix_]

Doesnt appear to bring back any results, I have put record in database that is 14 days prior to todays date..

[r937]

doing it with an equality requires using the DATE() function, to extract just the date portion

but the problem with using a function is that it requires a table scan, whereas the date range (lower and upper bounds) allows an index to be used

if you could show your query, and dump the table and a few rows, i'll double-check your query for you

[_matrix_]

My bad.. it does work.. The server is on a different time zone, so a day or so out..

[_matrix_]

Cant seem to find a way to alter the query so that..

Bring back results that are 30 days old only if there are no entries within the last 30 days..

[r937]

Bring back results that are 30 days old only if there are no entries within the last 30 days..

use two queries

first query gets results within the last 30 days, and if there are none, then second query gets results older than 30 days

[_matrix_]

Hmm, i'm still lost on it.. This is for appointments.. If 30 days has passed since last appointment dispatch email, but exclude anyone that has another appointment booked between 30 days ago and into the future.. if that makes sence?

The code below sends out to anyone who had an appointment logged 30 days ago, even if they have made another appointment for after that..

$sql = mysql_query("SELECT t.*, c.*
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
WHERE timeslot >= CURRENT_DATE - INTERVAL 31 DAY
AND timeslot  < CURRENT_DATE - INTERVAL 30 DAY");

[squeal]

Perhaps no one had an appointment on 31st August.

[_matrix_]

An example of results of the above script (these people had an appointment 30 days ago, so would be emailed..)

Fri 31 Aug 2012 @ 8:30 am -> Alan
Fri 31 Aug 2012 @ 12:30 pm -> Martin
Fri 31 Aug 2012 @ 3:00 pm -> Craig
Fri 31 Aug 2012 @ 9:00 am -> Sue
Fri 31 Aug 2012 @ 2:00 pm -> Kellie
Fri 31 Aug 2012 @ 9:30 am -> Karen


But these 2 names allready have up coming appointments..

Thu 13 Sep 2012 @ 1:00 pm -> Kellie
Fri 14 Sep 2012 @ 3:30 pm -> Martin

So need to be excluded from the results above.. Basically looking for people that havnt been back in the last 30 days and havnt booked any further appointments.. A note saying we havnt seen you in awhile, if you would like to make another appointment, call.....

[_matrix_]

The answer:

$sql = mysql_query("SELECT c.clientid, c.email, c.name, MAX(DATE(t.timeslot)) as latest 
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
GROUP BY c.clientid
HAVING MAX(DATE(timeslot)) = CURDATE() - INTERVAL 30 DAY");

Maybe helpfull to someone else..