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"
}
}
}
}
}
}