Need help on DateHistogram queries

Elastic Stack Elasticsearch
1

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**)


[
      {
        "_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!

2

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