looking to extract daily count of distinct logins for application between 6am and 8pm

Question

I have a table that stores user login info including the login_time (in mm/dd/yyyy hh:mm:ss ) along with their user_id. I want to be able to list out the daily count of unique user logins for each week/month etc daily from 6am to 8pm. im just not sure how to be able to sort by just the TIME and not full datetime. Basic query to get for the current day after 6am

SELECT COUNT(DISTINCT user_id) 
 FROM users_logins
 WHERE users_logins.login_time >= '2020-04-16 13:00:00'

which returns the unique # of user logins for the day. i want to be able to run for all days between 6am and 8pm. so i can generate report with details. Any thoughts on how best to do this ?

thanks let me know

Answer 1

MySQL recognizes datatype TIME, so you can just do:

where time(login_time) >= '06:00:00' and time(login_time) < '20:00:00'

To get the daily count of distinct logins within that timeframe, you would go:

select date(login_time) login_day, count(distinct user_id)
from user_logins
where time(login_time) >= '06:00:00' and time(login_time) < '20:00:00'
group by login_date

---

If needed, you can handle a time offset as follows:

select date(login_time - interval 7 hour) login_day, count(distinct user_id)
from user_logins
where 
    time(login_time - interval 7 hour) >= '06:00:00' 
    and time(login_time - interval 7 hour) < '20:00:00'
group by login_date