# How to calc login,logout duration inside special time span

**URL:** <https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201>\
**Category:** Elasticsearch\
**Created:** [June 28, 2024, 10:44am UTC](https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201 "2024-06-28T10:44:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![moryaden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/moryaden/32/135648_2.png) [@moryaden](https://discuss.elastic.co/u/moryaden)\
**Post date:** [June 28, 2024, 10:44am UTC](https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201/1 "2024-06-28T10:44:03Z")

</div>

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:

```auto
{"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.

```auto
{
  "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.

---

<div class="post-metadata">

**Author:** ![moryaden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/moryaden/32/135648_2.png) [@moryaden](https://discuss.elastic.co/u/moryaden)\
**Post date:** [June 29, 2024, 11:17am UTC](https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201/2 "2024-06-29T11:17:44Z")

</div>

```auto
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

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [June 30, 2024, 3:00pm UTC](https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201/3 "2024-06-30T15:00:06Z")

</div>

What version are you on?

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

---

<div class="post-metadata">

**Author:** ![moryaden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/moryaden/32/135648_2.png) [@moryaden](https://discuss.elastic.co/u/moryaden)\
**Post date:** [July 1, 2024, 2:27am UTC](https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201/4 "2024-07-01T02:27:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [July 2, 2024, 4:30pm UTC](https://discuss.elastic.co/t/how-to-calc-login-logout-duration-inside-special-time-span/362201/5 "2024-07-02T16:30:54Z")

</div>

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...

> **[Runtime fields | Elasticsearch Guide \[7.17\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/7.17/runtime.html)**

The runtime fields would look alot like the code above.

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