Text field to convert as date and perform math Operation

[Sathish22 (Sathish Thumma)]

Hi All,

I need to convert a text field to date data type and perform a math operation on it

something like this

Process ID	Process Name	Start Time	End Time	Difference
1	Process1	2021-01-12T13:16:20.19Z	2021-01-12T13:16:25.16Z	Start Time-End Time
2	Process2	2021-01-12T13:20:20.19Z	2021-01-12T13:21:20.15Z	Start Time-End Time
3	Process3	2021-01-12T13:21:25.19Z	2021-01-12T13:21:40.16Z	Start Time-End Time
4	Process4	2021-01-12T13:21:20.15Z	2021-01-12T13:21:35.15Z	Start Time-End Time

Here the problem is StartTime and End time are Text data type

Please help me

[wylie (Wylie Conlon)]

You need to use a painless script to do this, which can be done using Kibana or using an Elasticsearch ingest pipeline. There are many examples of doing this on the painless docs for date manipulation: https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-datetime.html

[Sathish22 (Sathish Thumma)]

Thank you for your response

There are errors after creating painless Scripted field

PFB image, I used to convert text to date

but it didn't work, please help what should I give in script

and input is displaying as numbers but my input data is (2021-01-12T13:16:20.198Z) in text format

[wylie (Wylie Conlon)]

What you're showing doesn't look right. The previous link that I sent gives you instructions on how to convert from the string representation of a date to number, by parsing the date. Dates are numeric in Elasticsearch, so you need to do this parsing.