Convert keyword field to date for range query

[NEWBIENEET]

One of the fields in the ES that my team used is "TIME_STAMP" which contains a timestamp data but in keyword format. I want to query data based on this value, is there any way to convert the keyword to date? I'm new to ES stuff, so please don't get mad with me.

Mapping info about TIME_STAMP field:

...
          "TIME_STAMP": {
                    "type": "keyword",
                    "eager_global_ordinals": true,
                    "normalizer": "...",
                    "copy_to": [
                        "..."
                    ]
                },
...

Data sample of TIME_STAMP field

...
          "TIME_STAMP": "2023-12-25 15:39:21.094",
...

Without create another field to store TIME_STAMP in date (or timestamp) format, is it possible to convert the datatype within the query?

As for why not stores in timestamp format in the first place (as I saw this mentioned in many posts), I don't know, when I joined the team, the field is there, and it seems I cannot change it myself.

[czek0]

Hi you can runtime mappings and painless scripting to convert the keyword to be in date form during query:

GET /my_index/_search
{
  "runtime_mappings": {
    "parsed_timestamp": {
      "type": "date",
      "script": {
        "source": """
          DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
          emit(ZonedDateTime.parse(doc['TIME_STAMP'].value, formatter).toInstant().toEpochMilli());
        """
      }
    }
  },
  "query": {
    "match_all": {}
  },
  "fields": ["parsed_timestamp"]
}

If you want to be able to search without creating a runtime mapping each time, you can create a new sup component called timestamp.data. Note you will have to reindex your data if you do the second method.

PUT /my_index_reindexed/_mapping
{
  "properties": {
    "TIME_STAMP": {
      "type": "keyword",  // existing field
      "fields": {
        "date": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss.SSS"
        }
      }
    }
  }
}
POST /_reindex
{
  "source": {
    "index": "my_index"
  },
  "dest": {
    "index": "my_index_reindexed"
  },
}

[NEWBIENEET]

I tried your first method, it first found a formatting error but after looking at the error cause, I tried adding formatter.withZone(ZoneId.of('GMT+7')) in formatter param and it works like a charm!

I'll look into the second method for a long-term solution.

Thank you!