MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE user_websocket_events
(
id integer,
user_id integer,
event varchar(20),
status varchar(20),
extra_attributes varchar(20),
created_at TIMESTAMP,
updated_at TIMESTAMP
);
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');
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;


id
user_id
status
created_at
duration
date


10816
69
online
2022-05-15 12:57:31
08:00:00
2022-05-15

10817
69
away
2022-05-15 20:57:31
02:00:00
2022-05-15

10818
69
online
2022-05-15 22:57:31
04:00:00
2022-05-15

10819
69
away
2022-05-16 02:57:31
01:00:00
2022-05-16

10820
69
unsubscribe
2022-05-16 03:57:31
null
2022-05-16


SELECT   `id`,
         `user_id`,
         `status`,
         `created_at`,
         CASE
                  WHEN Date(lag_created_at)=Date(created_at) THEN created_at
                  ELSE Timestamp(Date(created_at))
         end new_starttime,
         CASE
                  WHEN Date(lead_created_at)=Date(created_at) THEN lead_created_at
                  WHEN lead_created_at is null then null
                  ELSE Addtime(Timestamp(Date(created_at)),'23:59:59')
         end AS new_endtime,
         Sec_to_time(Timestampdiff(second,
         CASE
                  WHEN Date(lag_created_at)=Date(created_at) THEN created_at
                  ELSE Timestamp(Date(created_at))
         end,
         CASE
                  WHEN Date(lead_created_at)=Date(created_at) THEN lead_created_at
                  WHEN lead_created_at is null then null
                  ELSE Addtime(Timestamp(Date(created_at)),'23:59:59')
         end )) AS duration,
         date
FROM     (
                  SELECT   `id`,
                           `user_id`,
                           `status`,
                           `created_at`,
                           (Lead(created_at) over (partition BY user_id ORDER BY created_at))                   AS lead_created_at,
                           coalesce(lag(created_at) over (partition BY user_id ORDER BY created_at),created_at) AS lag_created_at,
                           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) )tmp
ORDER BY `id` ASC;


id
user_id
status
created_at
new_starttime
new_endtime
duration
date


10816
69
online
2022-05-15 12:57:31
2022-05-15 12:57:31
2022-05-15 20:57:31
08:00:00
2022-05-15

10817
69
away
2022-05-15 20:57:31
2022-05-15 20:57:31
2022-05-15 22:57:31
02:00:00
2022-05-15

10818
69
online
2022-05-15 22:57:31
2022-05-15 22:57:31
2022-05-15 23:59:59
01:02:28
2022-05-15

10819
69
away
2022-05-16 02:57:31
2022-05-16 00:00:00
2022-05-16 03:57:31
03:57:31
2022-05-16

10820
69
unsubscribe
2022-05-16 03:57:31
2022-05-16 03:57:31
null
null
2022-05-16


SELECT   `id`,
         `user_id`,
         `status`,
         `created_at`,
         CASE
                  WHEN Date(lag_created_at)=Date(created_at) THEN created_at
                  ELSE Timestamp(Date(created_at))
         end new_starttime,
         CASE
                  WHEN Date(lead_created_at)=Date(created_at) THEN lead_created_at
                  ELSE Addtime(Timestamp(Date(created_at)),'23:59:59')
         end AS new_endtime,
         Sec_to_time(Timestampdiff(second,
         CASE
                  WHEN Date(lag_created_at)=Date(created_at) THEN created_at
                  ELSE Timestamp(Date(created_at))
         end,
         CASE
                  WHEN Date(lead_created_at)=Date(created_at) THEN lead_created_at
                  ELSE Addtime(Timestamp(Date(created_at)),'23:59:59')
         end )) AS duration,
         date
FROM     (
                  SELECT   `id`,
                           `user_id`,
                           `status`,
                           `created_at`,
                           (Lead(created_at) over (partition BY user_id ORDER BY created_at))                   AS lead_created_at,
                           coalesce(lag(created_at) over (partition BY user_id ORDER BY created_at),created_at) AS lag_created_at,
                           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) )tmp
ORDER BY `id` ASC;


id
user_id
status
created_at
new_starttime
new_endtime
duration
date


10816
69
online
2022-05-15 12:57:31
2022-05-15 12:57:31
2022-05-15 20:57:31
08:00:00
2022-05-15

10817
69
away
2022-05-15 20:57:31
2022-05-15 20:57:31
2022-05-15 22:57:31
02:00:00
2022-05-15

10818
69
online
2022-05-15 22:57:31
2022-05-15 22:57:31
2022-05-15 23:59:59
01:02:28
2022-05-15

10819
69
away
2022-05-16 02:57:31
2022-05-16 00:00:00
2022-05-16 03:57:31
03:57:31
2022-05-16

10820
69
unsubscribe
2022-05-16 03:57:31
2022-05-16 03:57:31
2022-05-16 23:59:59
20:02:28
2022-05-16
id	user_id	status	created_at	duration	date
10816	69	online	2022-05-15 12:57:31	08:00:00	2022-05-15
10817	69	away	2022-05-15 20:57:31	02:00:00	2022-05-15
10818	69	online	2022-05-15 22:57:31	04:00:00	2022-05-15
10819	69	away	2022-05-16 02:57:31	01:00:00	2022-05-16
10820	69	unsubscribe	2022-05-16 03:57:31	null	2022-05-16