I have this table on MySQL database version 5.5.62 (remote hosting) for 2022 year.

------------------------------------------------------------------------------------
| number_access_total_year | number_access_month | number_access_day | access_user |
------------------------------------------------------------------------------------
|                    16350 | 1363                | 45                | D1          |
|                    14870 | 1239                | 41                | D2          |
|                    13591 | 1133                | 37                | D3          |
|                    13364 | 1114                | 37                | D4          |
|                    12324 | 1027                | 34                | D5          |
------------------------------------------------------------------------------------

This table is the summary of the accesses registered for each user.

Each user access is stored in database table even if it is multiple times a day.

In the access table are stored:

1. user name (dt_user),
2. access day (dt_access),
3. access time (tm_access)

i. e.

---------------------------------------------
| dt_user | dt_access  | tm_access | dt_ID  |
---------------------------------------------
| D1      | 2022-08-19 | 11:18:36  | 120716 |
| D5      | 2022-03-21 | 23:18:36  | 120715 |
| D5      | 2022-03-21 | 12:24:13  | 120714 |
| D5      | 2022-03-21 | 08:46:55  | 120713 |
| D3      | 2022-01-16 | 04:41:11  | 120712 |
---------------------------------------------
5 rows in set (0.11 sec)

I would need to convert this data into days, because I need to know the number of days when the user has login and the number of days he has never login.

Any suggestion?

Any help really appreciated.

CodePudding user response：

1.You have to find the day differences between 2 dates (todaydate, date that you want to start tracking the days)
example: SELECT DATEDIFF(CURDATE(),"2017-06-25");

2. Find the distinct dates from dt_access and get their count.

3. The number of days that user has loged in is the count of point no.2, the number of days that user has not logged in is: totalDays (of point no.1) - count of logged in users (point no.2)