Retrieving a date range in c#

[donElk]

Hello,
I am trying to get a list of records from my Elasticsearch client using C#. Within my Elasticsearch index I have a field called startdate. I am looking to query a date range based off this code.

        List<Employee> respose = Connection.EsClient().Search<Employee>(s => s
                .Index("employee")
                .AllTypes()
                .From(0)

                .Query(q => q
                      .DateRange(c => c
                           .Field("startdate")
                           .Format("yyyy-MM-dd")
                           .GreaterThanOrEquals("2015/01/01")
                           .LessThanOrEquals("2015/01/15")
                       )
                )
           ).Documents.ToList();

When I run this code nothing returns back from Elasticsearch. Also, if I increase the date range it returns back everything which is incorrect. It is kind of like it is ignoring the DateRange filter. I am currently running Elasticsearch 6.4.2. Anything helps, thanks!

[dakrone (Lee Hinman)]

Hi DonElk,

Would you be able to generate the JSON that is being sent for this query? It is difficult to diagnose any problems from the C# without seeing what the client library is actually sending.

[donElk]

Raw:
"{\r\n "from": 0,\r\n "size": 10,\r\n "query": {\r\n "range": {\r\n "startdate": {\r\n "_name": "startDate_query",\r\n "gte": "01/01/2016 11:25:06",\r\n "lte": "15/01/2016 11:25:06",\r\n "format": "dd/MM/yyyy hh:mm:ss||yyyy"\r\n }\r\n }\r\n }\r\n}"

Clean:
"{"from": 0, "size": 10, "query": {"range": {"startdate": {"_name": "startDate_query", "gte": "01/01/2016 11:25:06", "lte": "15/01/2016 11:25:06", "format": "dd/MM/yyyy hh:mm:ss||yyyy"}}}}"

This is the same code, just different parameters used to call Elasticsearch Api. Thank you!

[donElk]

By the way, I am also seeing this same behavior in Postman. For example:
Request:
{
"query": {
"range" : {
"startdate" : {
"format": "mm/dd/yyyy hh:mm:ss",
"gte": "02/08/2016 11:25:06",
"lt": "03/08/2016 11:25:06"
}
}
}
}

Response:
"hits":[
"startdate": "03/28/2016 13:26:50",
"startdate": "03/05/2016 13:48:28",
"startdate": "03/08/2016 13:25:56",
"startdate": "03/02/2016 14:50:48",
"startdate": "06/03/2016 13:24:01",
"startdate": "11/08/2016 15:25:03"
]

[donElk]

Just realized the startdate 'type' is set to text. I will probably need to change this to date to fixed this issue. The index data was pulled from a SQL database which was parsed to Elasticsearch using Logstash. The data type for 'startdate' within SQL is of type datetime.

[dakrone (Lee Hinman)]

That would definitely do it.