How to calc login,logout duration inside special time span

Elastic Stack Elasticsearch
1

Guys, I already struggled from es docs for days,
but not a better solution could be found.
Need your professional opinions!!

I have these recorded data lines:

{"uid":1, "login": "2024-06-28 17:00:00", "logout":"2024-06-28 18:00:00"}
{"uid":1, "login": "2024-06-28 18:20:00", "logout":"2024-06-28 18:50:00"}
{"uid":2, "login": "2024-06-28 18:00:00", "logout":"2024-06-28 18:10:00"}
....

what I have to answer is, inside time range: 2024-06-28 17:30:00 ~~ 2024-06-28 18:30:00 ,
how many users have stayed longer than 30 minutes.

it's hard to calculate the accurate seconds/minutes when time range become a dynamic search parameter.

I do found that, I could calc a real-online-duration in specified time range with "script_fields" feature, but that is a secment data, which should be sum ed togather then filtered out.

{
  "size": 3,
  "_source": true,
  "script_fields": {
    "my_duration_sec": {
      "script": {
        "source": """
                long li = doc['LoginTime'].value;
                long lo = doc['LogoutTime'].value;
                if (li < params['begin']) {
                  li = params['begin'];
                }
                if (lo == 0 || lo > params['end']) {
                  lo = params['end'];
                }
                return (lo - li);
                """,
        "params": {
          "begin": 1719482400,
          "end": 1719483000
        }
      }
    }
  }

How could I achieve this?

Thanks a lot.

2 
POST /_sql
{
  "query": """ 
    select UserId, sum(duration) as duration1 from (
      select UserId, endTime - beginTime as duration from (
        SELECT UserId,
          case when LoginTime < 1719577800 then 1719577800 else LoginTime end as beginTime,
          case when LogoutTime = 0 then 1719578220 when LogoutTime > 1719578220 then 1719578220 else LogoutTime end as endTime
        FROM "access-detail-2024-06-28"
          where RoomId = 100294 and LoginTime < 1719578700 and LogoutTime > 1719576000
    )) group by UserId having duration1 > 600
    """,
  "filter": {
    "match": {
      "RoomId": "100294"
    }
  }
}

The most nearest answer we have come out.

This will output all users quolified with online time

3

What version are you on?

If you are on newer version ESQL should be a good approach

4

Thanks, I am using version 7.17

It include the SQL feature.

I managed to query data with SQL but that still have a little distance to my purpose.

5

SQL and ESQL are two different things (but ESQL is in 8.13 etc)

If I were you I might look at creating a runtime field elaspsed_time that would calculate the difference for each document / record...

The runtime fields would look alot like the code above.

Then you query could return that ... and check elaspsed_time greater than 30 mins