CODEWITHSUNDEEP
c#elasticsearchnest
Srijani Ghosh
Srijani Ghosh

Reputation: 4216

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

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 ?

Upvotes: 1

Views: 3652

Answers (2)

Rob
Rob

Reputation: 9979

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.

Upvotes: 3

Stefano Branco
Stefano Branco

Reputation: 668

In theory, it should work like that. The following Elastic Query

"query": {
    "range": {
        "publication_date": {
            "gte": "2012-05-29T01:00:00",
            "lte": "2012-05-29T01:00:00", 
            "time_zone": "+01:00"
        }
    }
}

gives me documents with 2012-05-29T00:00:00 as results.

Just to make sure, are you using the From and To parts at the same time? In that case, I believe the second one would overwrite the first one and should be consolidated into just one query.

What does the serialized query look like if you enable EnableDebugMode() in the ConnectionSettings (or manually serialize the query with client.RequestResponseSerializer.SerializeToString(...);). Maybe the c# date is not as expected?

Upvotes: 0

Related Questions