Time-range search query help

Elastic Stack Elasticsearch
1

Hi, I need help in time-range search aggregations, I am trying to figure out aggregated visits for every 2 hours below is the query which doesn't work. help needed on this

POST index_name/_search
{
  "size": 0, 
  "query": {"bool": {"must": [
    {"match": {
      "org_name": "ABC"
    }},
    {
      "range": {
        "visit_time": {
          "format": "dd-MM-yyyy HH:mm:ss.SSS",
          "from": "01-01-2019 00:00:00.000",
          "to": "31-12-2019 23:59:50.000"
        }
      }
    }
  ]}},
  "aggs": {
    "range": {
      "date_range": {
        "field": "visit_time",
        "format": "HH:mm:ss.SSS",
        "ranges": [
          {"from": "00:00:00.000", "to": "02:00:00.000", "key": "00-02"},
          {"from": "02:00:00.000", "to": "04:00:00.000", "key": "02-04"},
          {"from": "04:00:00.000", "to": "06:00:00.000", "key": "04-06"},
          {"from": "06:00:00.000", "to": "08:00:00.000", "key": "06-08"},
          {"from": "08:00:00.000", "to": "10:00:00.000", "key": "08-10"},
          {"from": "10:00:00.000", "to": "12:00:00.000", "key": "10-12"},
          {"from": "12:00:00.000", "to": "14:00:00.000", "key": "12-14"},
          {"from": "14:00:00.000", "to": "16:00:00.000", "key": "14-16"},
          {"from": "16:00:00.000", "to": "18:00:00.000", "key": "16-18"},
          {"from": "18:00:00.000", "to": "20:00:00.000", "key": "18-20"},
          {"from": "20:00:00.000", "to": "22:00:00.000", "key": "20-22"},
          {"from": "22:00:00.000", "to": "23:59:59.000", "key": "22-24"}
        ],
        "keyed": true
      },
      "aggs": {
        "unique_visits": {
          "cardinality": {
            "field": "visit_id"
          }
        }
      }
    }
  }
}

I know i can do like this, but it gives lot of values for 1 year duration which need to be re-aggregated again

GET <index_name>/_search
{
          "size": 0,
          "query": {"bool": {"must": [
              {"match": {
                  "org_name": "ABC"
              }},
              {"range": {
                  "patient_visit_time": {
                      "format": "dd-MM-yyyy",
                      "from": "01-01-2019",
                      "to": "31-12-2019"
                  }
              }
              }]}},
          "aggs": {
              "over_time": {
                  "date_histogram": {
                      "field": "visit_time",
                      "interval": "120m"
                  },
                  "aggs": {
                      "unique_visits": {
                          "cardinality": {
                              "field": "visit_id"
                          }
                      }
                  }
              }
          }
      }
2

Can you try this

"aggs": {
    "date_range": {
      "terms": {
        "script": {
          "lang": "painless",
          "source": "int h = doc['dt'].value.getHourOfDay()/2; return (h*2) + '-' + (h*2 + 2);"
        }
      }
    }
  }
3

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