Converting epoch time format to datetime format using script

Elastic Stack Elasticsearch
1 
In elastic searach there is a index name personalprofile11 and there is a field named createdDate in epoch format , integer data type. but I want to make it datetime format query  Datehistogramaggregation and using that converted datetime field want to count the data on database or daily base,  the chatgpt give me the following query but it not working


curl -X POST "http://localhost:9200/myprofile11/_search?scroll=5m&pretty" -H 'Content-Type: application/json' -d '{
  "size": 10,
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "createdDate": 0
          }
        }
      ],
      "filter": [
        {
          "range": {
            "createdDate": {
              "gte": 1631320967,
              "lte": 1631407367
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "date_terms_agg": {
      "scripted_metric": {
        "init_script": "state.timestamps = []",
        "map_script": "if (doc.containsKey(\"createdDate\") && doc[\"createdDate\"].size() > 0) { state.timestamps.add(doc[\"createdDate\"].value * 1000) }",
        "combine_script": "def merged = []; for (state in states) { merged.addAll(state.timestamps) }; return merged",
        "reduce_script": "def merged = []; for (state in states) { merged.addAll(state) }; return merged"
      },
      "aggs": {
        "date_histogram_agg": {
          "date_histogram": {
            "field": "createdDate",
            "calendar_interval": "1d",
            "format": "yyyy-MM-dd",
            "min_doc_count": 1
          }
        }
      }
    }
  }
}'



and it showing me the following error
{
  "error" : {
    "root_cause" : [
      {
        "type" : "script_exception",
        "reason" : "compile error",
        "script_stack" : [
          "... ddAll(state.timestamps) }; return merged",
          "                             ^---- HERE"
        ],
        "script" : "def merged = []; for (state in states) { merged.addAll(state.timestamps) }; return merged",
        "lang" : "painless",
        "position" : {
          "offset" : 74,
          "start" : 49,
          "end" : 89
        }
      }
    ],
    "type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "myprofile11",
        "node" : "oNajg00ZSK6sNQzE-8Lp-Q",
        "reason" : {
          "type" : "script_exception",
          "reason" : "compile error",
          "script_stack" : [
            "... ddAll(state.timestamps) }; return merged",
            "                             ^---- HERE"
          ],
          "script" : "def merged = []; for (state in states) { merged.addAll(state.timestamps) }; return merged",
          "lang" : "painless",
          "position" : {
            "offset" : 74,
            "start" : 49,
            "end" : 89
          },
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "unexpected token [';'] was expecting one of [<EOF>]."
          }
        }
      }
    ]
  },
  "status" : 400
}
can anyone help me how can i do that please as

Blockquote

2

Welcome to our community! :smiley:

Can you edit your post please, it's a bit hard to read with it all being code formatted.

3

is it okay now? sorry for my unformatted post, this my first post here. If it is seems still messy , would like to tell me any online tools to make it formatted then post, I actually tried my best. Thank you

4

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