Finding records with timestamp more than a given time ago

[missyevil]

Hi,

I've been trying to get a query working that returns a list of users stored in my database, who last logged in over 90 days ago.

I've got this so far:

[code]SELECT jobseekers.forename, jobseekers.surname, jslogin.email, jslogin.js_last_login
FROM jobseekers, jslogin
WHERE jslogin.js_last_login <= SUBDATE(NOW(), INTERVAL 90 DAY);[/code]

But when I run it I get an empty set, when there really should be some entries.

Am still a total newbie, but I hope its clear what I'm trying to do!!

Thank you in advance for any help.

[wickning1]

First of all, you are missing a join condition between your two tables. You still have a problem though, because your date subtraction was correct and you shouldn't have gotten an empty set. What data type is the js_last_login column? tinytext, varchar, timestamp, datetime?

[code]SELECT s.forename, s.surname, j.email, j.js_last_login
FROM jobseekers s INNER JOIN jslogin j ON s.userid=j.userid
WHERE j.js_last_login <= NOW() - INTERVAL 90 DAY[/code]
This is how I wrote the query.. you need to replace that "ON s.userid=j.userid" with a true join condition.

[missyevil]

TIMESTAMP... Should it be a simple DATETIME?

[fenway]

Don't get me started -- everything should be a DATETIME. TIMESTAMP columns have never been properly implemented, even in 5.0.

[wickning1]

That won't break the query though. Something else is wrong. Maybe you have a PHP problem. Try running this query through phpMyAdmin or something similar.

[fenway]

Of course -- I figured that the poster used your solution irrespective of column type.

[missyevil]

OK, I changed the js_last_login column type to DATETIME (thanks for the tip - didn't realise there was a problem with TIMESTAMP), and now it is working!!

Thanks :)