Runtime field issue creating a date/time field

[Garry (Garry Robertson)]

I am creating a new date field through Lens.
The original field is coming through as text in the format '2022-10-25T16:04:35.320'
In the Set Value field i have...

if (doc.containsKey('sql.metrics.string.starttime')) {
def source = doc['sql.metrics.string.starttime'].value;
if (source != "") {
emit(source);

}

}

However, I am getting the following error...

cannot implicitly cast def [java.lang.String] to long

Any ideas?

[Marco_Liberati]

The error message is trying to say that the returned value is a String but it was expecting a long value (Date in milliseconds).

Changing the emit part to transform the String should make it work:

if (doc.containsKey('sql.metrics.string.starttime')) {
  def source = doc['sql.metrics.string.starttime'].value;
  if (source != "") {
    emit(
      ZonedDateTime.parse(source)
      .toInstant()
      .toEpochMilli()
    );
  }
}

Here you can find more references about DateTime manipulations: Using Datetime in Painless | Painless Scripting Language [master] | Elastic

[Garry (Garry Robertson)]

Hi Marco, thanks for the reply.

I am now getting the following error using your code...

date_time_parse_exception: Text '2022-06-13T15:20:29.707' could not be parsed at index 23

[Marco_Liberati]

The date string is missing the "Time Zone designator" (part of ISO8601).
If the date strings are UTC you can append a "Z" at the end of it:

if (doc.containsKey('sql.metrics.string.starttime')) {
  def source = doc['sql.metrics.string.starttime'].value;
  if (source != "") {
    emit(
      ZonedDateTime.parse(source + "Z")
      .toInstant()
      .toEpochMilli()
    );
  }
}

[Garry (Garry Robertson)]

Hi Marco,
that works....until i am in Discovery which then throws up an error...

"type": "illegal_state_exception",
"reason": "A document doesn't have a value for a field! Use doc[].size()==0 to check if a document is missing a field!"

Any ideas?

[Marco_Liberati]

That is an additional condition to check before emitting in case there are no values.

if (doc.containsKey('sql.metrics.string.starttime') && doc['sql.metrics.string.starttime'].size() != 0) {
  def source = doc['sql.metrics.string.starttime'].value;
  if (source != "") {
    emit(
      ZonedDateTime.parse(source + "Z")
      .toInstant()
      .toEpochMilli()
    );
  }
}