MySQL 5.5 How to do know the number of days when the user has login and the number of days he has never login

Hi,

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.

to count the dates that a user did not log in, you need a table of dates, which will be the left table in a LEFT OUTER JOIN

1 Like
SELECT dt_user, COUNT(DISTINCT dt_access) AS days 
FROM   access
WHERE  dt_access BETWEEN "2022-01-01" AND "2022-12-31"
GROUP  BY dt_user

(Do your own math on “days he never logged in” - it’s the number of days he logged in subtracted from the total number of days in the year.)

1 Like

actually, subtracted from the total number of days in the time period requested

suppose it’s the number of days since Jan 6 2021 and the first indictment

“do your own math” is a lot easier with a date table

;o)

1 Like

Yes, I assumed the user meant the year, given that they said their table was for a given year.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.