Jump to content


Photo

Finding records with timestamp more than a given time ago


  • Please log in to reply
6 replies to this topic

#1 missyevil

missyevil
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 30 March 2006 - 07:39 PM

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:

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

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.

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 30 March 2006 - 09:48 PM

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?

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
This is how I wrote the query.. you need to replace that "ON s.userid=j.userid" with a true join condition.

#3 missyevil

missyevil
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 30 March 2006 - 10:35 PM

TIMESTAMP... Should it be a simple DATETIME?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 March 2006 - 04:32 AM

Don't get me started -- everything should be a DATETIME. TIMESTAMP columns have never been properly implemented, even in 5.0.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 31 March 2006 - 12:50 PM

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.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 31 March 2006 - 07:39 PM

Of course -- I figured that the poster used your solution irrespective of column type.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 missyevil

missyevil
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 01 April 2006 - 01:02 PM

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 :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users