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 
  row (10816, 69, 'subscribe', 'online', null, '2022-05-15 12:57:31', '2022-05-14 10:57:37'),
  row (10817, 69, 'away', 'away', null, '2022-05-15 20:57:31', '2022-05-14 10:57:37'),
  row (10818, 69, 'online', 'online', null, '2022-05-15 22:57:31', '2022-05-14 10:57:37'),
  row (10819, 69, 'away', 'away', null, '2022-05-16 02:57:31', '2022-05-14 10:57:37'),
  row (10820, 69, 'unsubscribe', 'unsubscribe', null, '2022-05-16 03:57:31', '2022-05-14 10:57:37');
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


id
status
date
created_at
ended_at


10816
online
2022-05-15
2022-05-15 12:57:31
2022-05-15 20:57:31.000000

10817
away
2022-05-15
2022-05-15 20:57:31
2022-05-15 22:57:31.000000

10818
online
2022-05-15
2022-05-15 22:57:31
2022-05-15 23:59:59.000000

10818
online
2022-05-16
2022-05-16 00:00:00
2022-05-16 02:57:31.000000

10819
away
2022-05-16
2022-05-16 02:57:31
2022-05-16 03:57:31.000000

10820
unsubscribe
2022-05-16
2022-05-16 03:57:31
2022-05-16 23:59:59.000000

id	status	date	created_at	ended_at
10816	online	2022-05-15	2022-05-15 12:57:31	2022-05-15 20:57:31.000000
10817	away	2022-05-15	2022-05-15 20:57:31	2022-05-15 22:57:31.000000
10818	online	2022-05-15	2022-05-15 22:57:31	2022-05-15 23:59:59.000000
10818	online	2022-05-16	2022-05-16 00:00:00	2022-05-16 02:57:31.000000
10819	away	2022-05-16	2022-05-16 02:57:31	2022-05-16 03:57:31.000000
10820	unsubscribe	2022-05-16	2022-05-16 03:57:31	2022-05-16 23:59:59.000000