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');