# Model data points with multiple timestamps

**URL:** <https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005>\
**Category:** Kibana\
**Created:** [May 31, 2018, 8:43am UTC](https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005 "2018-05-31T08:43:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Erik\_M](https://avatars.discourse-cdn.com/v4/letter/e/3bc359/32.png) [@Erik\_M](https://discuss.elastic.co/u/Erik_M)\
**Post date:** [May 31, 2018, 8:43am UTC](https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005/1 "2018-05-31T08:43:21Z")

</div>

Hey!  
I have a question regarding modelling, and/or searching, data with multiple timestamps. Structure of the data:  
The data is a list of licenses, each with two timestamps: Start date (starts) and End date (ends).  
During this time period a license is considered "Active".

I would like to do two things. First:  
I would like to for a given time period (e.g. 2018-05-30 TO 2018-06-05) count the amount of Active licenses. I have figured out this is semi doable (?) with the json queries but I would like to have it as a metric visualisation. The following json query

```auto
{
  "query": {
    "bool": {
      "must": {
        "range": {
          "starts" : {
              "lte" : "2018-05-30 00:00:00"
            }
        }
      },
      "filter": {
        "range": {
          "ends": {
            "gte" : "2018-05-30 00:00:00"
          }
        }
      }
    }
  }
}

```

Will give me the correct amount of active licenses for that specific date. But again, I would like to have it as a metric visualisation and also be dependant on the time range of the dashboard. I.e.  
STARTS lte Dashboard ENDTIME  
AND  
ENDS gte Dashboard STARTTIME

Secondly:  
I would like to model these amounts over time in a time series. I.e. for each time period (days, weeks etc.) model the amount of licenses in a time series.

---

<div class="post-metadata">

**Author:** ![tsullivan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tsullivan/32/31077_2.png) [@tsullivan](https://discuss.elastic.co/u/tsullivan)\
**Post date:** [May 31, 2018, 5:49pm UTC](https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005/2 "2018-05-31T17:49:35Z")

</div>

You can just use a query\_string query to make a filter for documents with start times above X date and end times below Y date. Example:

```auto
starts:[2017-01-01 TO *] AND ends:[* TO 2017-12-31]

```

For the metric visualization, choose `Count` as your metric aggregation, and `filters` as the bucket aggregation. You'll just need 1 filter bucket, and put in the query string query for that filter. Example:

![image](https://us1.discourse-cdn.com/elastic/original/3X/6/3/63ba013f3585c52c15fcd2b73985484c2180957b.png)

Note that in my sample data, my start timestamp is `@timestamp` and my end timestamp is `@timestamp_end`, but use your own field names (obviously) 😃

For the time series question, when you make a date histogram in Elasticsearch, you have to choose a time field to use for the bucket keys. If you choose your start time, you can get a count of the licenses that have started per-time-bucket. If you choose the end time, you'll get a count of the licenses that end per-time-bucket. But it seems like there's not a way to get a count for the number of licenses that are active per-time-buckets, because you can't aggregate using a date math expression for the bucket keys.

Since this is the Kibana category and not the Elasticsearch category though, I'm not the best expert on querying Elasticsearch. You are more than welcome to ask this question in the Elasticsearch category.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [May 31, 2018, 6:00pm UTC](https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005/3 "2018-05-31T18:00:50Z")

</div>

You may be able to do something [like this](https://discuss.elastic.co/t/display-concurrency-in-data-on-kibana/26006/3).

---

<div class="post-metadata">

**Author:** ![Erik\_M](https://avatars.discourse-cdn.com/v4/letter/e/3bc359/32.png) [@Erik\_M](https://discuss.elastic.co/u/Erik_M)\
**Post date:** [June 5, 2018, 1:58pm UTC](https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005/4 "2018-06-05T13:58:04Z")

</div>

Thanks a lot for the help, your suggestion drove me closer to my goal.  
However I would like the date ranges not to be fix, but affected by time picker. I.e. starts should be  
[\* TO timepicker\_max\_time] AND ends should be [timepicker\_min\_time TO \*] is that possible?

On another note, I can't seem to search for time ranges at all within the discover function, started a separate thread for that: [Can't query for date or date range](https://discuss.elastic.co/t/cant-query-for-date-or-date-range/134378/2).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 3, 2018, 1:58pm UTC](https://discuss.elastic.co/t/model-data-points-with-multiple-timestamps/134005/5 "2018-07-03T13:58:08Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.