Metricbeat SQL module date fields

Elastic Stack Kibana
1

I am ingesting some sql queries via the metricbeat sql module, including date/time fields.
I would like to use these fields on a Lens graph, but cannot as they are ingested as a string format.
Is there any way to get Kibana to recognise that these are date/time fields which can be used to graph on in a similar way i can with timestamp field?

2

As mentioned at similar question posted a few days ago, you can use runtime fields in Kibana through the Create field option in Data Views where you can use Painless to interact with your data to format a date.

Peek 2022-03-08 17-40

If you want to do that at ingestion time you can set up an ingest pipeline to generate your date fields using also Painless.

3

Thanks for the tip, but I can't seem to covert a string date field to a new date field.
I get the following error. (attached screenshot)
This is a datetime field from sql that i am trying to convert to an elastic datetime field to graph on.

CreateField01
CreateField01567×745 39.9 KB

4

Take a look to this guide with plenty of examples on how to work with date and time with Painless. You are probably interested in the first section about parsing strings into date & time objects.