String type conversion to date type

[Rachana_Pole]

Hi all,
I have a field that provides the date information but is coming is of String Data Type.
Ex: metrics.database.state.my_session_time 2020-Aug-19 04:40:53
I want to convert it into a date format. I tried a few date mapping formats from https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html but was not able to convert. i also tried using scripted fields as suggested on Converting a string date to a Date field using scripted fields in kibana but it threw the following error:

      {
    "root_cause": [
     {
      "type": "script_exception",
      "reason": "runtime error",
      "script_stack": [
       "java.base/java.text.DateFormat.parse(DateFormat.java:395)",
       "new SimpleDateFormat('yyyy-MMM-dd HH mm ss').parse(doc['metrics.database.state.my_session_time.keyword'].value).getTime();",
       "                                                                                                                 ^---- HERE"
      ],
      "script": "new SimpleDateFormat('yyyy-MMM-dd HH mm ss').parse(doc['metrics.database.state.my_session_time.keyword'].value).getTime();",
      "lang": "painless"
     }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
     {
      "shard": 0,
      "index": "nw-conc",
      "node": "BR7csYvwSLyw5aSg7KCTZwq223",
      "reason": {
       "type": "script_exception",
       "reason": "runtime error",
       "script_stack": [
        "java.base/java.text.DateFormat.parse(DateFormat.java:395)",
        "new SimpleDateFormat('yyyy-MMM-dd HH mm ss').parse(doc['metrics.database.state.my_session_time.keyword'].value).getTime();",
        "                                                                                                                 ^---- HERE"
       ],
       "script": "new SimpleDateFormat('yyyy-MMM-dd HH mm ss').parse(doc['metrics.database.state.my_session_time.keyword'].value).getTime();",
       "lang": "painless",
       "caused_by": {
        "type": "parse_exception",
        "reason": "Unparseable date: \"\""
       }
      }
     }
    ]
   }

Can somebody please help me here? Thanks in adv

[flash1293 (Joe Reuter)]

For your scripted fields problem:

The "reason" field in these error messages is always the most helpful:

@Rachana_Pole

"reason": "Unparseable date: \"\""
 

It seems like your data contains documents which don't include metrics.database.state.my_session_time.keyword - this can be fixed for example by checking whether that's the case before attempting to parse and returning null if it's an empty string (like in this case). Your scripted field has to be able to successfully run for all documents, otherwise your search requests will fail.

That being said, solving this in the mapping is a much better approach (especially for performance reasons). If you have trouble setting it up correctly, folks in the Elasticsearch forum will be happy to help: Elasticsearch - Discuss the Elastic Stack Please post your question over there as well.