# Need help on DateHistogram queries

**URL:** <https://discuss.elastic.co/t/need-help-on-datehistogram-queries/355430>\
**Category:** Elasticsearch\
**Created:** [March 14, 2024, 5:02pm UTC](https://discuss.elastic.co/t/need-help-on-datehistogram-queries/355430 "2024-03-14T17:02:09Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Laksh\_0009](https://avatars.discourse-cdn.com/v4/letter/l/7993a0/32.png) [@Laksh\_0009](https://discuss.elastic.co/u/Laksh_0009)\
**Post date:** [March 14, 2024, 5:02pm UTC](https://discuss.elastic.co/t/need-help-on-datehistogram-queries/355430/1 "2024-03-14T17:02:09Z")

</div>

I need a help in building a query, I have two time fields in a index in same formats,

First I need to **groupBy**"returnCompletedDate" field but by rounding the time part to 00:00:00, like "2024-02-21 12:00:00" will be  
"2024-02-21 00:00:00" similarly for other "returnCompletedDate",  
Secondly, I need to take the average of same "returnCompletedDate" by difference with "returnDeliveryedTime" like for example:

`("2024-02-21 00:00:00" - "2024-02-20 11:49:07" ) + ("2024-02-21 00:00:00" - "2024-02-17 11:09:07") / number of returnCompletedDate (i.e. for 2024-02-21 which is **2** )`

```auto

[
      {
        "_source": {
          "returnDeliveryedTime": "2024-02-21 12:00:00",
          "returnCompletedDate": "2024-02-20 11:49:07",
        }
      },
      {
        "_source": {
          "returnDeliveryedTime": "2024-02-21 10:09:00",
          "returnCompletedDate": "2024-02-17 11:09:07",
        }
      },
      {
        "_source": {
          "returnDeliveryedTime": "2024-03-03 13:55:49",
          "returnCompletedDate": "2024-03-06 12:00:00"
        },
       {
        "_source": {
          "returnDeliveryedTime": "2024-03-01 13:55:49",
          "returnCompletedDate": "2024-03-06 13:50:40"
        },
        {
        "_source": {
          "returnDeliveryedTime": "2024-03-02 13:55:49",
          "returnCompletedDate": "2024-03-06 13:55:47"
        }
]

```

Thanks in advance!

---

<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:** [April 11, 2024, 5:02pm UTC](https://discuss.elastic.co/t/need-help-on-datehistogram-queries/355430/2 "2024-04-11T17:02:44Z")

</div>

Closed on April 11, 2024, 5:02pm UTC

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