Elastic search fail to do a timestamp range query

Question

i need to do a query within certain time range ,

First of all, i want to do a query like

    {
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "13000020"
          }
        },
        {
          "range": {
            "timestampstring": {
              "lte": "2020-10-05 15:22:58.537"
            }
          }
        }

      ]
    }
  }
}

and the results was

{
    "took": 15,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 12,
            "relation": "eq"
        },
        "max_score": 2.0,
        "hits": [
            {
                "_index": "test",
                "_type": "test12",
                "_id": "WvNJl3UBy18_Kc9Pl1tu",
                "_score": 2.0,
                "_source": {
                    "hdrId": 13000020,
                    "timestampstring": "2020-11-05 15:22:58.537",
                    "DevieId": "624232489",
                    "type": "data"
                }
            },
            {
                "_index": "test",
                "_type": "test12",
                "_id": "jvOSmHUBy18_Kc9PK3qp",
                "_score": 2.0,
                "_source": {
                    "hdrId": 13000020,
                    "timestamp": 1604582511655,
                    "timestampstring": "2020-11-05 21:21:51.655",
                    "type": "data"
                }
            }
        ]
    }
}

Can anyone pinpoint which part i was doing wrong?

secondly, i fail to do the example in this https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-daterange-aggregation.html

how can the above example suit my application , thanks

Jeff

At this moment i am trying to do in Postman, here is the setup

GET http://myip:9200/test/dev/_search and do i need to do the index here?

{
  "mappings": {
    "properties": {
      "timestampstring": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss.SSS"
      }
    }
  }
}

and it comes

{
    "error": {
        "root_cause": [
            {
                "type": "parsing_exception",
                "reason": "Unknown key for a START_OBJECT in [mappings].",
                "line": 2,
                "col": 15
            }
        ],
        "type": "parsing_exception",
        "reason": "Unknown key for a START_OBJECT in [mappings].",
        "line": 2,
        "col": 15
    },
    "status": 400
}

Add the mapping for the index. – Nishant Nov 25 '20 at 09:58 — Nishant, Nov 25 '20 at 09:58

ESCoder · Accepted Answer · 2020-11-25T10:14:33.267

You might have not set the index mapping for timestampstring. To know more about date formats refer to this

Adding a working example with index data, mapping, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "timestampstring": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss.SSS"
      }
    }
  }
}

Index Data:

{
  "hdrId": 13000020,
  "timestamp": 1604582511655,
  "timestampstring": "2020-11-05 21:21:51.655",
  "type": "data"
}
{
  "hdrId": 13000020,
  "timestampstring": "2020-11-05 15:22:58.537",
  "DevieId": "624232489",
  "type": "data"
}

Search Query:

Now running the same search query, you will get your desired result

{
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "13000020"
          }
        },
        {
          "range": {
            "timestampstring": {
              "lte": "2020-10-05 15:22:58.537"
            }
          }
        }
      ]
    }
  }
}

Search Result:

"hits": []

You can apply Date range aggregation, in the following way:

{
  "aggs": {
    "range": {
      "date_range": {
        "field": "timestampstring",
        "format": "yyyy-MM-dd HH:mm:ss.SSS",
        "ranges": [
          {
            "to": "now-1M"       
          },
          {
            "from": "now-1M"
          }
        ]
      }
    }
  }
}

The above query will create two range buckets, the first will "bucket" all documents dated prior to 1 month ago, and the second will "bucket" all documents dated since 1 month ago. Since in the index data, there is no document that is dated prior to 1 month, so the doc_count of the first bucket is 0 and that of the second bucket is 2

Search Result:

"aggregations": {
    "range": {
      "buckets": [
        {
          "key": "*-2020-10-25 10:10:07.665",
          "to": 1.603620607665E12,
          "to_as_string": "2020-10-25 10:10:07.665",
          "doc_count": 0
        },
        {
          "key": "2020-10-25 10:10:07.665-*",
          "from": 1.603620607665E12,
          "from_as_string": "2020-10-25 10:10:07.665",
          "doc_count": 2
        }
      ]
    }
  }

how to do the index mapping part in python? and where to do the index mapping ? — Man Man Yu, Nov 25 '20 at 10:19
for the second solution, i got this ==> error "reason": "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [timestampstring] in order to load field data by uninverting the inverted index. Note that this can use significant memory." — Man Man Yu, Nov 25 '20 at 10:26
@ManManYu you might have not set the index mapping for `timestampstring` field (as given in the answer above). Once you set the index mapping for `timestampstring` then you will be able to perform date range aggregation correctly. — ESCoder, Nov 25 '20 at 10:34
@ManManYu I am not aware of python syntax :( But you can refer to this answer, to set index mapping in python https://stackoverflow.com/a/31638685/10348758 — ESCoder, Nov 25 '20 at 10:36
i am using POSTMAN to test the ES, so what is the steps to do the index mapping? i edit the question for the index one — Man Man Yu, Nov 25 '20 at 10:37
@ManManYu Using postman, you can create an index as `PUT /index_name`, and then in the body use the same index mapping as given in the answer — ESCoder, Nov 25 '20 at 10:40
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/225085/discussion-between-bhavya-and-man-man-yu). — ESCoder, Nov 25 '20 at 10:40

Elastic search fail to do a timestamp range query

1 Answers1

Linked