MySQL使用lead函数计算从日期到日期结束的持续时间
P粉885035114
P粉885035114 2024-03-28 14:43:36
0
1
368

我有一个表存储用户不同会话的记录(订阅、取消订阅、离开、在线)。我可以使用以下给定的查询来计算每个会话的持续时间。 有一种情况,假设用户在“2022 年 5 月 15 日晚上 11:00:00”开始在线会话,之后第二天他在“2022 年 5 月 16 日下午 02:00”离开:上午 00 点” 总在线时间为 3 小时,我得到的日期是 2022 年 5 月 15 日的最后一行。

但我需要这样 5月15日至“2022年5月15日23:59:59”在线时间为1小时,5月16日至“2022年5月16日00:00:00至2022年5月16日02:00” :00 AM”,在线2小时。因此,作为响应,它应该返回 5 月 15 日的 1 小时和 5 月 16 日的 2 小时,而不是 5 月 15 日总共返回 3 小时。

我正在使用lead函数从created_at列获取持续时间,有什么方法可以限制lead函数计算持续时间直到下一个created_at直到23:59:59。

这是我的工作查询。我正在使用最新的 MySQL(8) 版本。

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;

这是一些示例数据。

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

P粉885035114
P粉885035114

全部回复(1)
P粉481815897

使用动态日历表按天分割会话

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 

dbfiddle

热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责声明 Sitemap
PHP中文网:公益在线PHP培训,帮助PHP学习者快速成长!