Jump to content

Archived

This topic is now archived and is closed to further replies.

missyevil

Finding records with timestamp more than a given time ago

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Don't get me started -- everything should be a DATETIME. TIMESTAMP columns have never been properly implemented, even in 5.0.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Of course -- I figured that the poster used your solution irrespective of column type.

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.