How to group documents by hour without day in elasticsearch?

Question

i have a application who a survey is asked every day by users, and i want to have average answers hours. I tried some request but i can't group all documents by hours, it's grouped by hour by day..

I do this :

{
 "aggs": {
      "byHour": {
          "date_histogram": {
              "field": "date",
              "interval": "hour",
              "format" : "H"

                }
            }
        }
    }
}

It's wrapped by hour but also by date, and i want day as ignored.

 [
        {
          "key_as_string": "0",
          "key": 1533945600000,
          "doc_count": 40,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "1",
          "key": 1533949200000,
          "doc_count": 345,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "23",
          "key": 1534028400000,
          "doc_count": 15,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "0",
          "key": 1534032000000,
          "doc_count": 0,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "1",
          "key": 1534035600000,
          "doc_count": 2,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        },
        {
          "key_as_string": "2",
          "key": 1534039200000,
          "doc_count": 3,
          "group_by_state": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": []
          }
        }
 ]

Mapping of type

{
  "myIndex": {
    "mappings": {
      "answer": {
        "properties": {
          "date": {
            "type": "date"
          },
          "lang": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "level": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "offset": {
            "type": "long"
          },
          "patientCaretrackId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "protocolId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "query": {
            "properties": {
              "constant_score": {
                "properties": {
                  "filter": {
                    "properties": {
                      "bool": {
                        "properties": {
                          "must": {
                            "properties": {
                              "term": {
                                "properties": {
                                  "questionId": {
                                    "type": "text",
                                    "fields": {
                                      "keyword": {
                                        "type": "keyword",
                                        "ignore_above": 256
                                      }
                                    }
                                  },
                                  "questionnaireId": {
                                    "type": "text",
                                    "fields": {
                                      "keyword": {
                                        "type": "keyword",
                                        "ignore_above": 256
                                      }
                                    }
                                  }
                                }
                              }
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          },
          "questionId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "questionnaireId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "surgeonId": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "value": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            },
            "fielddata": true
          }
        }
      }
    }
  }
}

Example of documents :

[
{
          "date": "2018-09-11T00:00:00.000Z",
          "lang": "fr",
          "level": "red",
          "offset": 21,
          "patientCaretrackId": "5b894b10a9f7afec73762113",
          "protocolId": "ptg-koos-long-v1",
          "questionnaireId": "j21",
          "surgeonId": "699362de-f040-4799-b1ea-53f5b4a2fe03",
          "value": "permanentes",
          "questionId": "frequence-douleur-2"
},
{
          "date": "2018-09-11T00:00:00.000Z",
          "lang": "fr",
          "level": "red",
          "offset": 21,
          "patientCaretrackId": "5b894b10a9f7afec73762113",
          "protocolId": "ptg-koos-long-v1",
          "questionnaireId": "j21",
          "surgeonId": "699362de-f040-4799-b1ea-53f5b4a2fe03",
          "value": "permanentes",
          "questionId": "frequence-douleur-2"
        }
]

It's possible to do this with an query Elasticsearch ?

Thank you,

Answer 1

You can use a terms aggregation with a script:

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script

GET /_search
{
    "aggs" : {
        "hours" : {
            "terms" : {
                "script" : {
                    "source": "doc['date'].getHour()",
                    "lang": "painless"
                }
            }
        }
    }
}

(Just to give you an idea, not sure at all about the script itself ..)

Answer 2

I found thx

{
 "size": 0,
 "aggs": {
      "byHour": {
          "date_histogram": {
              "field": "date",
              "interval": "hour",
              "format" : "H",
              "keyed": true,
              "time_zone": "+02:00"
                }
            }
        }
}

The response :

{
    "took": 9,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 2796,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "byHour": {
            "buckets": {
                "17": {
                    "key_as_string": "17",
                    "key": 1536159600000,
                    "doc_count": 2006
                },
                "18": {
                    "key_as_string": "18",
                    "key": 1536163200000,
                    "doc_count": 790
                }
            }
        }
    }
}