How to select rows in every day between two dates using MySQL-CodePudding

I want to keep track of users logged in every day between two dates. Let's say I have a table my_table like this:

user_id	login_datetime
1	2021-10-02 10:00:00
1	2021-10-02 12:00:00
2	2021-10-02 12:20:00
1	2021-10-03 17:00:00
1	2021-10-04 22:00:00
2	2021-10-04 23:00:00

and given date range is from '2021-10-02' to '2021-10-04'.

I want to get user_id = 1 in this case, because user_id = 2 is not logged in at '2021-10-03'

result

user_id	login_date
1	2021-10-02
1	2021-10-03
1	2021-10-04

Is there any solution for this?

CodePudding user response：

One approach uses aggregation:

SELECT user_id
FROM my_table
WHERE login_datetime >= '2021-10-02' AND login_datetime < '2021-10-05'
GROUP BY user_id
HAVING COUNT(DISTINCT DATE(login_datetime)) = 3;  -- range has 3 dates in it

Demo

The HAVING clause asserts that any matching user must have 3 distinct dates present, which would imply that such a user would have login activity on all dates from 2021-10-02 to 2021-10-04 inclusive.

Edit:

To get the exact output in your question, you may use:

SELECT DISTINCT user_id, DATE(login_datetime) AS login_date
FROM my_table
WHERE user_id IN (
    SELECT user_id
    FROM my_table
    WHERE login_datetime >= '2021-10-02' AND login_datetime < '2021-10-05'
    GROUP BY user_id
    HAVING COUNT(DISTINCT DATE(login_datetime)) = 3
);