MySQL uses lead function to calculate duration from date to end of date

Question

I have a table that stores records of different user sessions (subscribe, unsubscribe, leave, online). I can calculate the duration of each session using the below given query. There is a scenario, suppose a user starts an online session on "May 15, 2022 11:00:00 PM" and after that the next day he leaves on "May 16, 2022 02:00 PM: 00 AM" Total online time is 3 hours and the date I get is May 15, 2022 for the last line.

But I need this The online time from May 15 to "23:59:59 on May 15, 2022" is 1 hour, and from May 16 to "00:00:00 on May 16, 2022 to 02:00 on May 16, 2022" 00":00 AM", online for 2 hours. So in response it should return 1 hour of May 15th and 2 hours of May 16th instead of 3 hours total on May 15th.

I am using the lead function to get the duration from the created_at column, is there any way to limit the lead function to calculate the duration until the next created_at until 23:59:59.

This is my working query. I'm using the latest MySQL(8) version.

select `id`, `user_id`, `status`, `created_at`,
 SEC_TO_TIME(TIMESTAMPDIFF(SECOND, created_at,
LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at))) as duration,
 date(created_at) as date from `user_websocket_events` as `all_status`
 where created_at between '2022-05-15 00:00:00' and '2022-05-16 23:59:59' and `status` is not null
 and user_id in (69) order by `id` asc;

Here is some sample data.

INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10816, 69, 'subscribe', 'online', null, '2022-05-15 12:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10817, 69, 'away', 'away', null, '2022-05-15 20:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10818, 69, 'online', 'online', null, '2022-05-15 22:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10819, 69, 'away', 'away', null, '2022-05-16 02:57:31', '2022-05-14 10:57:37');
INSERT INTO user_websocket_events (id, user_id, event, status, extra_attributes, created_at, updated_at) VALUES (10820, 69, 'unsubscribe', 'unsubscribe', null, '2022-05-16 03:57:31', '2022-05-14 10:57:37');

Answer 1

Use a dynamic calendar to split sessions by day

with recursive calendar as (
      select timestamp('2022-05-01 00:00') start_time, timestamp('2022-05-01 23:59:59')  end_time, 1 id 
      union all
      select start_time + interval 1 day, end_time + interval 1 day, id+1
      from calendar
      where id < 100
)
select e.id,  e.status, date(greatest(c.start_time, e.created_at)) date,
   greatest(c.start_time, e.created_at) as created_at,
   least(c.end_time, e.ended_at) as ended_at
from (
   select `id`, `user_id`, `status`, `created_at`,
     -- a session end is restricted to the end of the requierd interval 
     LEAD(created_at, 1, '2022-05-16 23:59:59') OVER (PARTITION BY user_id ORDER BY created_at) as ended_at
   from `user_websocket_events` 
   where 
     -- only sessions started within the requierd interval 
     created_at between '2022-05-15 00:00:00' and '2022-05-16 23:59:59' and `status` is not null
     and user_id in (69) 
   ) e
join calendar c on c.start_time < e.ended_at and e.created_at < c.end_time
order by id;

db<>fiddle