mysql query - date subtraction

Question

• I am trying to query users who has been active in the last 3 years.
• I am adjusting a query I have used for years that I know works fine. The only part of the query that I am trying to add on is the part below that is outside the grey code. I can't get this part correct.
• The column that I am querying the date for (lastactivity) is stored in int(10) format and I don't think I will be changing it because it is a VBulletin column and I don't want that change to affect so many other queries.

$query = "SELECT user.userid AS id3, user.lastactivity AS lastactivity1, DATE_FORMAT(FROM_UNIXTIME(user.lastactivity), '%Y %m %d') AS 'lastactivity2', socialgroup.groupid AS id1, socialgroupicon.groupid AS id2, socialgroupicon.dateline AS picdate, name, zip1, city1, state1, socialgroup.dateline AS groupdate, creatoruserid, members, picturecount, visible, paid FROM socialgroup LEFT JOIN socialgroupicon ON socialgroupicon.groupid=socialgroup.groupid LEFT JOIN user ON user.userid=socialgroup.creatoruserid WHERE type != 'inviteonly' AND state1 = '$region' AND lastactivity2 < UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 3 YEAR)) ORDER BY paid DESC, lastactivity2 DESC, id1 ASC LIMIT 100 ";

Answer 1

The solution was that I need to change the LEFT JOIN on the user table to an INNER JOIN since the lastactivity column, which I am adding to there WHERE clause, is in that table. (I also need to change it to "lastactivity" instead of "lastactivity2).

$query = "SELECT user.userid AS id3, user.lastactivity AS lastactivity1, DATE_FORMAT(FROM_UNIXTIME(user.lastactivity), '%Y %m %d') AS 'lastactivity2', socialgroup.groupid AS id1, socialgroupicon.groupid AS id2, socialgroupicon.dateline AS picdate, name, zip1, city1, state1, socialgroup.dateline AS groupdate, creatoruserid, members, picturecount, visible, paid FROM socialgroup INNER JOIN user ON user.userid=socialgroup.creatoruserid LEFT JOIN socialgroupicon ON socialgroupicon.groupid=socialgroup.groupid WHERE type != 'inviteonly' AND state1 = '$region' AND lastactivity > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 3 YEAR)) ORDER BY paid DESC, lastactivity2 DESC, id1 ASC LIMIT 100 ";

Answer 2

if you are trying to get entries that were active within the last 3 years, your comparison should be a greater than:

lastactivity2 > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 3 YEAR)) 

Time moves forward, newer times are a bigger number than older times.

What you probably thought was that you wanted to compare 3 years to the time interval between today and the last activity, but you are comparing absolute timestamps, not differences.