MySQL menggunakan fungsi petunjuk untuk mengira tempoh dari tarikh hingga akhir tarikh

Question

Saya mempunyai jadual yang menyimpan rekod sesi pengguna yang berbeza (langgan, nyahlanggan, keluar, dalam talian). Saya boleh mengira tempoh setiap sesi menggunakan pertanyaan yang diberikan di bawah. Terdapat senario, katakan pengguna memulakan sesi dalam talian pada "15 Mei 2022 11:00:00 PTG" dan selepas itu pada hari berikutnya dia keluar pada "16 Mei 2022 02:00 PTG: 00 PG" Jumlah masa dalam talian ialah 3 jam dan tarikh yang saya dapat ialah 15 Mei 2022 untuk baris terakhir.

Tetapi saya perlukan ini Masa dalam talian dari 15 Mei hingga "23:59:59 pada 15 Mei 2022" ialah 1 jam dan dari 16 Mei hingga "00:00:00 pada 16 Mei 2022 hingga 02:00 pada 16 Mei 2022" 00 ":00 AM", dalam talian selama 2 jam, jadi sebagai balasan, ia sepatutnya kembali 1 jam pada 15 Mei dan 2 jam pada 16 Mei dan bukannya 3 jam secara keseluruhan untuk 15 Mei

.

Saya menggunakan fungsi petunjuk untuk mendapatkan tempoh daripada lajur create_at, adakah terdapat cara untuk mengehadkan fungsi lead untuk mengira tempoh sehingga create_at seterusnya sehingga 23:59:59.

Ini adalah pertanyaan kerja saya. Saya menggunakan versi MySQL(8) terkini.

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;

Berikut adalah beberapa sampel 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

Pisah sesi mengikut hari menggunakan kalendar dinamik

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