How to sort after date with own format and bool query

Elastic Stack Elasticsearch
1

Hey guys,

I'm using c# nest library to create my index with special DateTime Format, because I need the exact millisecond time from my date

How it looks like in c#:

public class IndexClass {
   ....
   [Date(Format = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSSZ")]
   [PropertyName("Valid From")]
   public DateTime ValidFrom { get; set; }
   ....
}

When I'm searching this index in elasticsearch via GET indexName I'm getting the expected result:

"indexName": {
    "aliases": {},
    "mappings": {
       "doc": {
          "properties": {
              ...
              "Valid From": {
                 "type": "date",
                 "format": "yyyy-MM-dd'T'HH:mm:ss.SSSSSSSZ"
              }
              ...
        }
    }
}
"settings": {
    "index": {
       "creation_date": "1544804170823",
       "number_of_shards": "5",
       "number_of_replicas": "1",
       "uuid": "1WAVhAhUR8aQssJWC5AlTg",
       "version": {
          "created": "123456"
        },
        "provided_name": "indexName"
    }
}

But when I'm query this index with sort after Valid From with a bool query it doesn't work anymore:

POST indexName/doc/_search
{
  "sort": [
    {
      "Valid From": {
        "order": "desc"
      }
    }
  ],
  "query": {
    "bool": {
      "must": [
        { "match_phrase": {
          "Environment": "myEnviroment"
        }},
        { "match_phrase": {
          "Element": "myElement"
        }},
        { "match_phrase": {
          "Group": "myGroup"
        }},
        { "match_phrase": {
          "Job": "myJob"
        }}
      ],
      "filter": {
        "range": {
          "Valid From": {
            "gte":    "2018-12-12T00:00:00.0000000Z",
            "lte":    "2018-12-12T23:59:59.9999999Z",
            "format": "yyyy-MM-dd'T'HH:mm:ss.SSSSSSSZ"  
          }
        }
      }
    }
  }
}

I have to match the Environment, element, group and job exactly, filter it after date and sort it desc.

The result is wrong:

1. "Valid From": "2018-12-17T03:17:06.0000019Z"
2. "Valid From": "2018-12-17T03:17:06.0000021Z"
3. "Valid From": "2018-12-17T03:17:06.0000016Z"
4. "Valid From": "2018-12-17T03:17:06.0000018Z"
5. "Valid From": "2018-12-17T03:17:06.0000022Z"
...

I would expect:

1. "Valid From": "2018-12-17T03:17:06.0000022Z"
2. "Valid From": "2018-12-17T03:17:06.0000021Z"
3. "Valid From": "2018-12-17T03:17:06.0000019Z"
4. "Valid From": "2018-12-17T03:17:06.0000018Z"
5. "Valid From": "2018-12-17T03:17:06.0000016Z"
...

How can I get my expected result? Is the bool query not optimal in my case?

Thanks for helping me guys :slight_smile:

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.