stocktrader stocktrader - 2 months ago 7
MySQL Question

mysql query - date subtraction


  • 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

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 ";