Strange behavior of range query in Elasticsearch

Question

My question is pretty simple. I have an ES index which contains field updated that is a UNIX timestamp. I only have testing records (documents) in my index, which were all created today.

I have a following query, which works well and (righfully) doesn't return any results when executed:

GET /test_index/_search
{
  "size": 1,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "updated": {
              "lt": "159525360"
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "updated": {
        "order": "desc",
        "mode": "avg"
      }
    }
  ]
}

So this is all ok. However, when I change timestamp in my query to lower number, I am getting multiple results! And these results all contain much larger values in updated field than 5000! Even more bafflingly, I am getting results with updated only being set in range of 1971 to 9999. So numbers like 1500 or 10000 behave corectly and I see no results. Query behaving strangely is below.

GET /test_index/_search
{
  "size": 100,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "updated": {
              "lt": "5000"
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "updated": {
        "order": "desc",
        "mode": "avg"
      }
    }
  ]
}

Btw, this is how my typical document stored in this index looks like:

{
    "_index" : "test_index",
    "_type" : "_doc",
    "_id" : "V6LDyHMBAUKhWZ7lxRtb",
    "_score" : null,
    "_source" : {
      "councilId" : 111,
      "chargerId" : "15",
      "unitId" : "a",
      "connectorId" : "2",
      "status" : 10,
      "latitude" : 77.7,
      "longitude" : 77.7,
      "lastStatusChange" : 1596718920,
      "updated" : 1596720720,
      "dataType" : "recorded"
    },
    "sort" : [
      1596720720
    ]
}

Here is a mapping of this index:

PUT /test_index/_mapping
{
    "properties": {
        "chargerId":  { "type": "text"},
        "unitId":  { "type": "text"},
        "connectorId":  { "type": "text"},
        "councilId": { "type": "integer"},
        "status": {"type": "integer"},
        "longitude" : {"type": "double"},
        "latitude" : {"type": "double"},
        "lastStatusChange" : {"type": "date"},
        "updated": {"type": "date"}
    }
}

Is there any explanation for this?

Answer 1

The default format for a date field in ES is strict_date_optional_time||epoch_millis. Since you haven't specified epoch_second, your dates were incorrectly parsed (treated as millis since epoch). It's verifiable by running this script:

GET test_index/_search
{
  "script_fields": {
    "updated_pretty": {
      "script": {
        "lang": "painless",
        "source": """
          LocalDateTime.ofInstant(
             Instant.ofEpochMilli(doc['updated'].value.millis),
             ZoneId.of('Europe/Vienna')
          ).format(DateTimeFormatter.ofPattern("dd/MM/yyyy HH:mm"))
          """
      }
    }
  }
}

Quick fix: update your mapping as follows:

{
  ...
  "updated":{
    "type":"date",
    "format":"epoch_second"
  }
}

and reindex.