Create time series with fields date as long mapping

Hi all, sorry for my poor english
I have a index with a eventTime field mapped as long,
So when I create a time series no date field is found by kibana.
Is there a way (with a pre script???) to say to kibana "consider that long as date field"? I know that I should reindex the index and do a mapping for that purpose.
But I wonder if exists a way more easy.
Regards

Hi @Giovanni_Di_Lembo,

Unfortunately, you can't automatically convert a long to a date type. However, you can make use of ES's multi-field mappings and set it to date:

This request below updates the mappings to add a sub-field eventTime.date of type date:

PUT <YOUR_INDEX>/_mappings
{
  "properties": {
    "eventTime": {
      "type": "long",
      "fields": {
        "date": {"type": "date", "format": "epoch_millis"}
      }
    }
  }
}

Now you'll be able to create a time-series index-pattern in Kibana using the eventTime.date field.

For this change to apply to all your docs, you'll need to force an update so all the documents are reindexed with the new mappings. Running the following request does the job:

POST <YOUR_INDEX>/_update_by_query
{
  "query": {
    "match_all": {}
  }
}

Alternatively, you can define a new index with the correct mapping and reindex all the content of your index to the new index.

2 Likes

The problem is I cannot reindex my docs, I'd like to access field currentDate in this mapping

"mappings" : {
  "properties" : {
    "_class" : {
      "type" : "text",
      "fields" : {
        "keyword" : {
          "type" : "keyword",
          "ignore_above" : 256
        }
      }
    },
    "activities" : {
      "type" : "nested",
      "properties" : {
        "uuid" : {
          "type" : "keyword"
        },
        "creationDate" : {
          "type" : "keyword"
        },
        "errorCode" : {
          "type" : "keyword"
        },
        "errorMessage" : {
          "type" : "keyword"
        },
        "jobId" : {
          "type" : "keyword"
        },
        "lastUpdate" : {
          "type" : "keyword"
        },
        "message" : {
          "type" : "keyword"
        },
        "modificationDate" : {
          "type" : "keyword"
        },
        "operationType" : {
          "type" : "keyword"
        }
      }
    }
    
  }
},

But actually kibana cannot access it
I created script as date script

doc['activities.creationDate'].value

But kibana says

Script is invalid. View script preview for details

Oh! I see activities is type: "nested", so I assume it's an array of objects.

In that case, you'll have to iterate through all the elements in the array. Also, because it's nested, you'll need to access the values from params._source instead of doc. This is a bit more costly regarding performance.

List dates = [];

for (int i = 0; i < params._source['activities'].length; ++i){
    def parsedDate = ZonedDateTime.parse(params._source['activities'].get(i)['creationDate']);
    dates.add(parsedDate);
}

return dates;

Mind in the script I'm converting my keyword (that looks like "2020-02-15T10:00:00.000Z") to a date. Check out these docs in case your documents have a different format: Using Datetime in Painless | Painless Scripting Language [7.11] | Elastic

I should warn you though: since the scripted fields are added after creating the index pattern, Kibana won't treat this index as a timeseries index, and the date picker will likely be disabled in many apps. I hope that's OK for your use case :slight_smile:

I've done something similar

 List dates = [];
 DateTimeFormatter dtf = DateTimeFormatter.ofPattern("YYYY-MM-dd 'T' HH:mm:ss.SSSZ");
for (int i = 0; i < params._source['activities'].length; ++i){
    def dateTime=params._source['activities'].get(i)['creationDate'];
    def parsedDate = ZonedDateTime.parse(dateTime,dtf);
    dates.add(parsedDate);
}

return dates;

And i got

"reason": "Text '2021-01-18 T 18:54:10.037+0000' could not be parsed: 
Unable to obtain ZonedDateTime from TemporalAccessor: 
{OffsetSeconds=0, DayOfMonth=18, 
WeekBasedYear[WeekFields[SUNDAY,1]]=2021, MonthOfYear=1},ISO 
resolved to 18:54:10.037 of type java.time.format.Parsed",

YYYY is week based year, which is not what you want here. Using yyyy instead, for year-of-era works.

You are the best!! Great :grinning:

1 Like

I solved syntax of my script with help stu help, but actually it hangs when I try to save

Can you check out the network inspector to see what's going on?

Got this

Can you try selecting the Type to date?

It is selected

oh! wait! what are those errors for HTTP requests? ERR_NAME_NOT_RESOLVED? Any connectivity issues?

No, sorry was a old session picture.
See this, ti hangs the button

Looking at the code, it looks like the page fails to remove the field from the previous group type to assign it to the new one date. Do you mind trying to create a new field instead?

I hoped in your script to avoid to create new field (it is mandatory to reindex my data I think)

Scripts are not indexed. AFAIK, creating a new scripted field does not require reindexing. Maybe I'm misunderstanding the situation. Can you provide more context and why you think you'll need to reindex if you create a new scripted field?

I just need a script field that transform a string mapped field in date field.
The problem is that string field (in format YYYY-MM-DD HH:MM:SS.SSSS ) is placed in a nested array of object and kibana do no see it when I 'm creating an histogram by date. For 2 reason it is nested and it is not a date
With your script kibana hangs with previous described error.
Thanks for your attention

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