Date Range search using NEST api for elastic search returning inappropriate results

Question

I am trying to use Date Range search in Elastic search using NEST API.

I know that ES stores the timestamp in UTC while doing integration with Nlog. However, I need to a Date Range search in this @timestamp field.

I wrote these following queries :

From Date Search:

qcd.DateRange(r => r
                    .Field(f => f.timestamp)
                    .GreaterThanOrEquals(searchFromDateTime)
                    .TimeZone("+02:00")
                );

To Date Search:

qcd.DateRange(r => r
                        .Field(f => f.timestamp)
                        .LessThanOrEquals(searchToDateTime)
                        .TimeZone("+02:00")
                    );

Here is the rest of the query:

searchResponse = (SearchResponse<SearchEventDto>)client.Search<SearchEventDto>(s => s
                    .Index("logstash-*")
                    .Type("logevent")
                    .Query(q => qcd)
                );

SearchFromDateTime or SearchToDateTime are c# dates.

Clearly, there is something wrong in the queries, because, it does not take into account the time difference.

For example, since I am CET time, if I give 28.06.2019 14:48 as Search From Date, it should search from 28.06.2019 12:48. Or, the same should happen in Search To date too.

Any idea, how I can achieve this ?

Answer 1

I've prepared a working example for you, maybe you will be able to find an issue with it in your case. If not, update the question with sample document you are expecting to find with your query.

class Program
{
    public class Document  
    {
        public int Id { get; set; }
        public DateTime Timestamp { get; set; }
    } 

    static async Task Main(string[] args)
    {
        var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
        var connectionSettings = new ConnectionSettings(pool);
        connectionSettings.DefaultIndex("documents");

        //only for debbuging purpose, don't use in production
        connectionSettings.DisableDirectStreaming();
        connectionSettings.PrettyJson();

        var client = new ElasticClient(connectionSettings);

        await client.Indices.DeleteAsync("documents");
        await client.Indices.CreateAsync("documents");

        var response = await client.IndexAsync(
            new Document {Id = 1, Timestamp = new DateTime(2010, 01, 01, 10, 0, 0)}, descriptor => descriptor);

        var searchResponse = await client.SearchAsync<Document>(s => s
            .Query(q => q
                .Bool(b => b
                    //I'm using date range in filter context as I don't want elasticsearch
                    //to calculate score for each document found,
                    //should be faster and likely it will be cached
                    .Filter(f =>
                        f.DateRange(dt => dt
                            .Field(field => field.Timestamp)
                            .LessThanOrEquals(new DateTime(2010, 01, 01, 11, 0, 0))
                            .TimeZone("+1:00"))))));

        //prints 1
        Console.WriteLine(searchResponse.Documents.Count);
    }
}

Hope that helps.