How to count active users using mysql

I’m trying to count active users in the last month by comparing the logged event against today’s date. So for example if a user logged in 2 days ago(09/01/2021), the query would evaluate to true since the logged in month 9 = todays month 9 AND logged year 2021 = todays year 2021. since its only using months and years, a row should be able to evaluate to true if they’ve logged in within the month. There are three tables, 1 with recorded login times, 1 with user information, and 1 that serves as a nexus table. My problem is that something evaluates to false and all my counts are 0. any suggestions? the 60*60*-5 is just an adjustment for the time zone

SELECT COUNT(DISTINCT E.userid) AS UsersThisMonth
FROM user U
JOIN roles R ON R.userid = U.id
LEFT JOIN events E ON E.userid = U.id AND E.siteid = R.siteid 
AND MONTH(FROM_UNIXTIME(E.time + (60*60* -5))) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL (60*60* -5) SECOND)) 
AND YEAR(FROM_UNIXTIME(E.time + (60*60* -5))) = YEAR(DATE_ADD(CURRENT_DATE(), INTERVAL (60*60* -5) SECOND))
WHERE R.role = 3
GROUP BY R.siteid  

Answer

Instead of comparing the month and year separately, get the timestamp for the beginning of the month and compare with that.

AND E.time >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01'))

You don’t need to add or subtract the timezone offset, the functions for converting between Unix timestamps and database times automatically adjusts.