DateHistogram Date Formats

[RobinRieger]

If I use the DateHistogram I keep getting a message that the search failed due to the date format.

The search is:

DateTime startDate = DateTime.UtcNow;
DateTime endDate = DateTime.UtcNow.AddDays(1);

.DateHistogram
    ("orders",
        h => h.Field(fi => fi.OrderDate)
            .Interval(DateInterval.Day)
            .Format("yyyy-MM-dd'T'HH:mm:ss")
            .MinimumDocumentCount(0)
            .ExtendedBounds(startDate, endDate)))))

And the message I get is:
failed to parse date field [2016-01-03T00:00:00Z] with format [yyyy-MM-dd'T'HH:mm:ss]

I had a look at the ExtendedBounds<T> that is used in the DateHistogramAggregationDescriptor and I think that it does not take into account the format when serialising. I.e. if I serialise a DateTime then I do get the format 2016-01-03T00:00:00Z.

Should the DateHistogram take the format into account when serialising the DateTime values? Alternatively, how can I send the ExtendedBounds with the date values in the format yyyy-MM-dd'T'HH:mm:ss?

Note: I tried calling .ToString("yyyy-MM-dd'T'HH:mm:ss") but extended bounds required a DateTime. I also could not find any tests that use the extended bounds and the format at the same time....

[russcam]

Thanks for reporting @RobinRieger 👍

[russcam]

This is a outstanding issue in Elasticsearch - see elastic/elasticsearch#9725.

To work around it for the time being, when specifying a format on the DateHistogram, include the date_optional_time as another format e.g.

DateTime startDate = DateTime.UtcNow;
DateTime endDate = DateTime.UtcNow.AddDays(1);

.DateHistogram
    ("orders",
        h => h.Field(fi => fi.OrderDate)
            .Interval(DateInterval.Day)
            // include the date_optional_time format in order to be able to parse 
            // the serialized extended bounds
            .Format("yyyy-MM-dd'T'HH:mm:ss||date_optional_time")
            .MinimumDocumentCount(0)
            .ExtendedBounds(startDate, endDate)))))

Will update the integration test to include this

[RobinRieger]

Thanks for the reply. Good find re the outstanding issue.

When I reported this I thought the problem was with the serializer in Nest, so Newtonsoft. I.e. I store all my documents with the date in format yyyy-MM-dd'T'HH:mm:ss and always pass all 'formats' in all queries using the same format. In the issue you linked to they wanted to retrieve in format MMM but pass in the extended bounds in yyyy-MM-dd, i.e. requiring two different format properties. In my case I just need the single format ( i don't need one for formatting and one for parsing) and figured Nest would serialise the query (including extended bounds) in the format I provided under the Format property (thus the property being used for both parsing and formatting). I guess the open issue if resolved would be more flexible, but nonetheless thought I would share my original thoughts for what its worth.

I will try the work around later, but should work and makes sense.

[russcam]

The problem is that Elasticsearch attempts to parse the date strings passed for extended_bounds using the format supplied in the query; the issue with this however is that the date strings in the query will have the format that the JsonNetSerializer is configured to use for DateTime serialization, by default ISO 8601, using the IsoDateTimeConverter.

To help Elasticsearch out here, passing the format date_optional_time in addition will allow it to parse the extended_bounds dates represented in ISO 8601, whilst still using the first format specified as the format for key_as_string. Does that make sense?

[russcam]

I've made a change to append date_optional_time format to the supplied format when extended_bounds is also specified.

[RobinRieger]

Thanks for the explanation. Yeah I tried it with a couple of different queries and date formats. Makes sense and the work around does resolve my issues.

👍 for the change. Way more user friendly. I could have seen myself forgetting to put on date_optional_time in the future lol