# How to sort after date with own format and bool query

**URL:** <https://discuss.elastic.co/t/how-to-sort-after-date-with-own-format-and-bool-query/161101>\
**Category:** Elasticsearch\
**Created:** [December 17, 2018, 9:19am UTC](https://discuss.elastic.co/t/how-to-sort-after-date-with-own-format-and-bool-query/161101 "2018-12-17T09:19:12Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nico92](https://avatars.discourse-cdn.com/v4/letter/n/a87d85/32.png) [@Nico92](https://discuss.elastic.co/u/Nico92)\
**Post date:** [December 17, 2018, 9:19am UTC](https://discuss.elastic.co/t/how-to-sort-after-date-with-own-format-and-bool-query/161101/1 "2018-12-17T09:19:12Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 14, 2019, 9:19am UTC](https://discuss.elastic.co/t/how-to-sort-after-date-with-own-format-and-bool-query/161101/2 "2019-01-14T09:19:15Z")

</div>

Closed on January 14, 2019, 9:19am UTC

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