# Filtering by date field with DSL returning invalid results

**URL:** <https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [March 3, 2023, 7:33pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969 "2023-03-03T19:33:12Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![pocketcolin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pocketcolin/32/117585_2.png) [@pocketcolin](https://discuss.elastic.co/u/pocketcolin)\
**Post date:** [March 3, 2023, 7:33pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/1 "2023-03-03T19:33:12Z")

</div>

I am trying to query one of my indexes for all records that have a date field (`labels.expiresAt`) set `gte` to `now`. In other words, the date field should be later than today. That seems super straightforward, but when I set the range to `"gte": "now"` I get 0 results and then I set it to `"lte"` and the results show up. In the example result below, you can see that the `expiresAt` value is `2023-06-03T17:31:50.000Z` which is 3 months from today so why would it show up in a search for dates `lte` now? What am I doing wrong here? This seems incredibly unintuitive if it isn't a bug.

DSL Query:

```auto
GET /logs-*/_search
{
  "query": {
    "range": {
      "labels.expiresAt": {
        "gte": "now"
      }
    }
  }
}

```

Hit that shows up but only if I set it to `lte` instead of `gte`:

```auto
{
  "_index": "xxx",
  "_id": "xxx",
  "_score": 1,
  "_source": {
    "host": {
      "hostname": "xxx"
    },
    "transaction": {
      "id": "xxx"
    },
    "message": "Example",
    "@timestamp": "2023-03-03T17:31:53.915Z",
    "service": {
      "name": "server"
    },
    "event": {
      "dataset": "server.log"
    },
    "ecs": {
      "version": "1.6.0"
    },
    "log.level": "info",
    "process": {
      "pid": 75
    },
    "labels": {
      "expiresAt": "2023-06-03T17:31:50.000Z"
    },
    "environment": "development",
    "trace": {
      "id": "xxx"
    },
    "@version": "1",
    "data_stream": {
      "type": "logs",
      "dataset": "generic",
      "namespace": "default"
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 3, 2023, 7:36pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/2 "2023-03-03T19:36:07Z")

</div>

What is the mapping of that field?

---

<div class="post-metadata">

**Author:** ![pocketcolin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pocketcolin/32/117585_2.png) [@pocketcolin](https://discuss.elastic.co/u/pocketcolin)\
**Post date:** [March 3, 2023, 7:40pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/3 "2023-03-03T19:40:17Z")

</div>

Where would I find that information?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 3, 2023, 7:41pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/4 "2023-03-03T19:41:38Z")

</div>

Get the index mappings using the [get mapping API](https://www.elastic.co/guide/en/elasticsearch/reference/8.6/indices-get-mapping.html).

If the field was incorrectly mapped as a `keyword` field I believe it would explain the behaviour you are seeing as `now` and the timestamps would be compared as strings and `now` would not be converted to a date.

---

<div class="post-metadata">

**Author:** ![pocketcolin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pocketcolin/32/117585_2.png) [@pocketcolin](https://discuss.elastic.co/u/pocketcolin)\
**Post date:** [March 3, 2023, 7:49pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/5 "2023-03-03T19:49:10Z")

</div>

Yes! That's the problem it looks like it was incorrectly mapped as a keyword field. I'm very new to field mapping. Any tips on how I can get started fixing this?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 3, 2023, 7:51pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/6 "2023-03-03T19:51:57Z")

</div>

You would need to create or update an index template with the mapping. It however looks like you are using ECS, so I am not sure whether that would break something. The index template would take care of new indices but as you can not change mappings of existing indices you would need to reindex any data that is already indexed.

How are you indexing data into Elasticsearch?

---

<div class="post-metadata">

**Author:** ![pocketcolin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pocketcolin/32/117585_2.png) [@pocketcolin](https://discuss.elastic.co/u/pocketcolin)\
**Post date:** [March 3, 2023, 7:54pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/7 "2023-03-03T19:54:47Z")

</div>

Data is currently coming in in ECS format as you noted. At the moment, I'm using a data\_stream from Logstash. I haven't setup any index templates or configuration for indexing so I guess I'm indexing data with whatever the default is in Elastic Cloud. Looks like I should probably go through this guide: [Set up a data stream | Elasticsearch Guide [8.6] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/set-up-a-data-stream.html)

---

<div class="post-metadata">

**Author:** ![pocketcolin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pocketcolin/32/117585_2.png) [@pocketcolin](https://discuss.elastic.co/u/pocketcolin)\
**Post date:** [March 3, 2023, 8:18pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/8 "2023-03-03T20:18:58Z")

</div>

holy jesus I figured out how to create all of the necessary index mapping stuff and reindex (moving everything from the main index to a new one, deleting the old one, and then moving it back) and now my query is working! That was exhasusting, but now I know how index mapping works and know what I need to do next time I run into a similar issue.

---

<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:** [March 31, 2023, 8:19pm UTC](https://discuss.elastic.co/t/filtering-by-date-field-with-dsl-returning-invalid-results/326969/9 "2023-03-31T20:19:39Z")

</div>

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