Elasticsearch Date Histogram buckets starts from wrong date

Question

I have ElasticSearch analytic query. It must aggregate sum by 7d buckets. Also I've use extended bounds. But result buckets starts from wrong date. Elastic 5.2.2 version. What I'm doing wrong?

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "doc.dateExecute": {
              "from": "2017-07-11",
              "to": "2017-07-31",
              "include_lower": true,
              "include_upper": true,
              "boost": 1.0
            }
          }
        }
      ],
      "disable_coord": false,
      "adjust_pure_negative": true,
      "boost": 1.0,
      "_name": "onDates"
    }
  },
  "aggregations": {
    "time": {
      "date_histogram": {
        "field": "doc.dateExecute",
        "interval": "7d",
        "offset": 0,
        "order": {
          "_key": "asc"
        },
        "keyed": false,
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2017-07-11",
          "max": "2017-07-31"
        }
      },
      "aggregations": {
        "incomingAmount": {
          "sum": {
            "field": "doc.incomingAmount"
          }
        },
        "outgoingAmount": {
          "sum": {
            "field": "doc.outgoingAmount"
          }
        }
      }
    }
  }
}

But buckets starts from "2017-07-06T00:00:00.000Z" and goes by 7d intervals to "2017-07-27T00:00:00.000Z". What I'm missing? Thanks!

Update 1

I'd try a many variants of query. Different timezones, ranges, disabling exteneded_bounds, intervals multiple 7 day. It's works good only for 1-3d intervals.

Update 2 I have tried 7.x. Seems to work fine on latest versions.

@val, "from": "2016-09-28", "to":"2017-10-28", extended_bounds removed. Anyway, first bucket starts on "key_as_string": "2016-09-29T00:00:00.000Z", with no respect to the first hit 2016-10-01 — Retardust, May 27 '17 at 06:27
what happens if you use dateExecute instead of doc.dateExecute? — deathyr, May 28 '17 at 04:05
@deathyr, dateExecute is nested doc's nested field. I'd try that anyway. Nothing changed for 7d case , but other tests are failed. — Retardust, May 28 '17 at 09:41

score 1 · Answer 1 · answered Jun 29 '17 at 19:22

Ok, after some conversation on elastic forum and register a bug:

1) Working with DateHistogram and ExtendedBounds is tricky in ES (5.2 at least) DateHistogram starts buckets from UnixEpoch 1970-01-01 so if you use days intervals (like 7d or 3d etc) but need to start bucket from the left bound - you need to set offset for on date of left border (in ms). 2) But we still have some bug on 5.2.2 version. With 7d intervals we have explicit first bucket on Thu (01-01-1970 is Thu). All other buckets with proper offset.

Lack of documentation and a lot of strange behavior :(

Also, as per this, I have tried setting min extended_bound to lower date in the range filter and offset also set to the same value in milliseconds. But it still gave the same results. The interval used is 90 days. — User3518958, Mar 13 '19 at 08:12

Elasticsearch Date Histogram buckets starts from wrong date

1 Answers1

Linked