Dealing with multiple date fields

[79g (Daniel)]

Hello,

We are dealing with an index that contains multiple date type fields. The structure, summarized, is below:

    ...
    "entry_date": {
      "type": "date",
      "ignore_malformed": false,
      "format": "yyyy-MM-dd HH:mm:ss"
    },
    "exit_date": {
      "type": "date",
      "ignore_malformed": false,
      "format": "yyyy-MM-dd HH:mm:ss"
    },
    ...

Imagine that the index stores information about shops capacity, including when a customer goes in and out of the store.

In any moment, how can we obtain how many customers does the shop have in?

Example:

Client 1:

• entry_date 2020-09-20 10:40:22
• exit_date 2020-09-20 11:22:06

Client 2:

• entry_date 2020-09-20 11:00:12
• exit_date 2020-09-20 11:15:32

At 2020-09-20 11:05:00 (for example) there were two customers inside the shop. How can I visualize that info with Kibana?

Appreciate any help.
Thanks in advance.

[spinscale (Alexander Reelsen)]

If you have a metric visualization that simply displays the number of documents found, in combination with a kibana KQL query for exit_date > 11:05 and entry_date < 11:05?

[79g (Daniel)]

That could work for a specific date (example 11:05).
But, could we extend that and create a date histogram with that info every moment?

Thanks @spinscale

[spinscale (Alexander Reelsen)]

You can also take a look at the date_range field type, which you can use with a date_histogram aggregation in Kibana. Console example:

PUT visitors
{
  "mappings": {
    "properties": {
      "visiting_time": {
        "type": "date_range"
      }
    }
  }
}

PUT visitors/_doc/client1
{
  "visiting_time" : {
    "gte" : "2020-09-20T10:40:22Z", 
    "lte" : "2020-09-20T11:22:06Z"
  }
}

PUT visitors/_doc/client2
{
  "visiting_time" : {
    "gte" : "2020-09-20T11:00:12Z", 
    "lte" : "2020-09-20T11:15:32Z"
  }
}

GET visitors/_search
{
  "size": 0,
  "aggs": {
    "by_day": {
      "date_histogram": {
        "field": "visiting_time",
        "fixed_interval": "15m"
      }
    }
  }
}

[79g (Daniel)]

Thanks for your help @spinscale, I think we are close to the solution .

As you show, it works perfect on Dev Console, and your aggregation returns the results I expect Thanks!

However, when trying to visualize on Kibana I can see that kibana index pattern does not map the visiting_time neither date nor date_range and it seems to be the reason why I can not make visualizations with that data.

I attach two screenshots about that:

However I can not perform the aggregation you suggested before. Am I misunderstanding anything?

PS: I'm using 7.9.1versions.

Thanks.

[spinscale (Alexander Reelsen)]

you're right, indeed this does not seem to be supported currently. You can follow the issue at https://github.com/elastic/kibana/issues/12751 for maybe also leave a comment - if you need Kibana support then this is not an option currently, sorry for that misdirection.

[79g (Daniel)]

Do you think there is another way to visualize on kibana?

I have been reading lots of discussions like Display concurrency in data on Kibana but that script didn't worked for me.