Question 20874 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Getting number of days user spent on each status

I have been trying to extract the number of days a particular user spent on each status in a month from the MySQL database table. The data is saved in log format which makes it a bit hard to work with. For e.g. I need to calculate the number of days the user 488 spent on each status in the month of June 2022 only.

user_id old_status  new_status      modified_on         
488     3               10          31/05/2022 10:03    
488     10              5           01/06/2022 13:05    
488     5               16          07/06/2022 16:06    
488     16              2           09/06/2022 08:26    
488     2               6           30/06/2022 13:51    
488     6               2           07/07/2022 09:44    
488     2               6           08/08/2022 13:25    
488     6               1           15/08/2022 10:37    
488     1               11          02/09/2022 13:48    
488     11              2           03/10/2022 07:26    
488     2               10          10/10/2022 10:17    
488     10              6           25/01/2023 17:50    
488     6               1           01/02/2023 13:46

The output should look like this:

The output should look like:
user    status  Days
488     5       6
488     16      2
488     2       21

I tried multiple ways to join the same table with itself in order to find the solution but no luck. Any help will be appreciated.

Tags:

2

Answers


  1. 0

    here is what I think you should do, first join the old_status field in the log table with the status table then use the DATEDIFF function to subtract modified_on(log table ) from created_at(or any other field in status that stores creation time) you can filter results using where clause to get certain users on certain dates

    this query might help (i don’t know the structure of your tables so if there is something wrong edit it to suit your needs)

    SELECT *,DATEDIFF(log.modified_at,st.created_at) AS spent_time_on_staus 
FROM log_status AS log JOIN status AS st ON st.id=log.old_status
WHERE log.user_id=488 AND EXTRACT(MONTH FROM st.created_at) = 6
    Login or Signup to reply.
  2. 0

    This is a suggestion to get you started. It will not get you all the way (since there are several status changes to and from the same status…)

    SELECT 
  shfrom.userid, 
  shfrom.new_status as statusName, 
  shfrom.modified_on as fromdate,
  shto.modified_on as todate,
  DATEDIFF(shto.modified_on, shfrom.modified_on) as days_spent_in_status
FROM 
  status_history as shfrom 
  INNER JOIN status_history as shto 
  ON shfrom.userid = shto.userid and shfrom.new_status = shto.old_status 
WHERE
  shfrom.modified_on < shto.modified_on
;

    I created a table based on your question and put in the data you provided, in mysql format:

    create table status_history(
  userid int, 
  old_status int, 
  new_status int, 
  modified_on datetime
);

insert into status_history values
(488, 3,10, '2022-05-31 10:03'),    
(488,10, 5, '2022-06-01 13:05'),    
(488, 5,16, '2022-06-07 16:06'),    
(488,16, 2, '2022-06-09 08:26'),    
(488, 2, 6, '2022-06-30 13:51'),    
(488, 6, 2, '2022-07-07 09:44'),    
(488, 2, 6, '2022-08-08 13:25'),    
(488, 6, 1, '2022-08-15 10:37'),    
(488, 1,11, '2022-09-02 13:48'),    
(488,11, 2, '2022-10-03 07:26'),    
(488, 2,10, '2022-10-10 10:17'),    
(488,10, 6, '2023-01-25 17:50'),    
(488, 6, 1, '2023-02-01 13:46');

    this produces this result, where the duration is the time spent:

    userid statusName fromdate todate days_spent_in_status
    488 10 2022-05-31 10:03:00 2022-06-01 13:05:00 1
    488 5 2022-06-01 13:05:00 2022-06-07 16:06:00 6
    488 16 2022-06-07 16:06:00 2022-06-09 08:26:00 2
    488 2 2022-06-09 08:26:00 2022-06-30 13:51:00 21
    488 6 2022-06-30 13:51:00 2022-07-07 09:44:00 7
    488 2 2022-06-09 08:26:00 2022-08-08 13:25:00 60
    488 2 2022-07-07 09:44:00 2022-08-08 13:25:00 32
    488 6 2022-06-30 13:51:00 2022-08-15 10:37:00 46
    488 6 2022-08-08 13:25:00 2022-08-15 10:37:00 7
    488 1 2022-08-15 10:37:00 2022-09-02 13:48:00 18
    488 11 2022-09-02 13:48:00 2022-10-03 07:26:00 31
    488 2 2022-06-09 08:26:00 2022-10-10 10:17:00 123
    488 2 2022-07-07 09:44:00 2022-10-10 10:17:00 95
    488 2 2022-10-03 07:26:00 2022-10-10 10:17:00 7
    488 10 2022-05-31 10:03:00 2023-01-25 17:50:00 239
    488 10 2022-10-10 10:17:00 2023-01-25 17:50:00 107
    488 6 2022-06-30 13:51:00 2023-02-01 13:46:00 216
    488 6 2022-08-08 13:25:00 2023-02-01 13:46:00 177
    488 6 2023-01-25 17:50:00 2023-02-01 13:46:00 7

    You still need to filter out the ones that are capturing an early status change with a later status change. I hope it gets you started.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search