The curse of dynamic dates

Elastic Stack Elasticsearch
1

I am trying to import a bunch of JSON documents that have dates with format:

2019-03-21T10:30:31.2Z
2018-10-26T23:42:27.3Z
2019-01-17T18:55:29.3Z
2018-09-15T11:26:16.1Z
2019-03-25T18:59:50.1Z
2019-03-04T00:21:27.1Z

which if I understand this page correctly

basic_ordinal_date_time

A formatter for a full ordinal date and time, using a four digit year and three digit dayOfYear: yyyyDDD'T'HHmmss.SSSZ .

Should match my dates.

On a brand new elasticsearch install, I get error:

ERROR 2019/04/19 09:53:37Bulk response item:
{
   "_index":"upbedv2.alerts",
   "_type":"_doc",
   "_id":"iQRvaG2cT95KHG46y",
   "status":400,
   "error":{
      "type":"mapper_parsing_exception",
      "reason":"failed to parse field [date] of type [date] in document with id 'iQRvaG2cT95KHG46y'",
      "caused_by":{
         "caused_by":{
            "reason":"Failed to parse with all enclosed parsers",
            "type":"date_time_parse_exception"
         },
         "reason":"failed to parse date field [2018-12-04T22:02:58.7Z] with format [strict_date_optional_time||epoch_millis]",
         "type":"illegal_argument_exception"
      }
   }
}

What is weird is that some of my documents made it into the index.

After googling I learn about dynamic dates, and think "maybe I need to specify an index template"
Via the kibana dev tools:

PUT _template/tpl
{
    "template": "myindex.*",
    "mappings": {
        "dynamic_date_formats" : [
            "basic_ordinal_date_time"
        ]
    }
}

Now my dates are not being recognized when I look at the mapping for the index.

I noticed how some of the dates don't have the trailing zeros for the miliseconds. Thought this might help:

PUT _template/tpl
{
    "template": "myindex.*",
    "mappings": {
        "dynamic_date_formats" : [
            "basic_ordinal_date_time",
            "yyyy-MM-dd'T'HH:mm:ss.SZ",
            "yyyy-MM-dd'T'HH:mm:ss.SSZ",
            "yyyy-MM-dd'T'HH:mm:ss.SSSZ"
        ]
    }
}

Dates are still not recognized.
What am I missing?

2

Elasticsearch 7 has switched from joda time to java.time. The ISO 8601 "Z" designation of the Zulu time zone is no longer valid when you use a Z in your date formatter. (Z indicates a RFC 822 4-digit time zone). Instead of Z you need to use an X:

{
  "mappings": {
    "dynamic_date_formats": [
      "basic_ordinal_date_time",
      "yyyy-MM-dd'T'HH:mm:ss.SX",
      "yyyy-MM-dd'T'HH:mm:ss.SSX",
      "yyyy-MM-dd'T'HH:mm:ss.SSSX"
    ]
  }
}

(By the way, the basic_ordinal_date_time format assumes that you have no dashes (-) between year-month-day, which is why that one will never match your dates that do have dashes).

1 Like
3

@abdon Thanks for the reply. Are you saying that I need to change my input file replacing Z with X?

If you are suggesting I change my input data, is there anything I can do in the elasticsearch mappings or config as I do not control my input data source.

If you are not suggesting an input format change, I am still unable to parse my dates with your suggested mapping.

I would expect this to work,

  PUT _template/tpl
  {
    "template": "myindex.*",
    "mappings": {
      "dynamic_date_formats" : [
        "yyyy-MM-dd'T'HH:mm:ss.S'Z'",
        "yyyy-MM-dd'T'HH:mm:ss.SS'Z'",
        "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
      ]
    }
  }

To ensure my format is correct I wrote a short test program based on the java.time.format.DateTimeFormatter documentation which successfully parsed the date

import java.time.*;
import java.time.format.DateTimeFormatter;

class Main {
  public static void main(String[] args) {
    System.out.println("Hello world!");

    String text   = "2018-12-23T04:48:08.123Z";
    String fmtstr = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'";
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern(fmtstr);

    LocalDate parsedDate = LocalDate.parse(text, formatter);

    System.out.println(parsedDate.toString());

  }
}

I also tried this syntax:

PUT _template/tpl
{
  "template": "myindex.*",
  "mappings": {
    "dynamic_date_formats" : [
      "yyyy-MM-dd'T'HH:mm:ss.S'Z'||yyyy-MM-dd'T'HH:mm:ss.SS'Z'||yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
    ]
  }
}

Error message with the || syntax

ERROR 2019/04/19 16:31:13Bulk response item:{
   "_index":"upbedv2.alerts",
   "_type":"_doc",
   "_id":"mh2caZb8PKcrzB9Jh",
   "status":400,
   "error":{
      "type":"mapper_parsing_exception",
      "reason":"failed to parse field [dateAck] of type [date] in document with id 'mh2caZb8PKcrzB9Jh'",
      "caused_by":{
         "caused_by":{
            "reason":"Failed to parse with all enclosed parsers",
            "type":"date_time_parse_exception"
         },
         "reason":"failed to parse date field [2018-12-09T19:46:45Z] with format [yyyy-MM-dd'T'HH:mm:ss.S'Z'||yyyy-MM-dd'T'HH:mm:ss.SS'Z'||yyyy-MM-dd'T'HH:mm:ss.SSS'Z']",
         "type":"illegal_argument_exception"
      }
   }
}
4

No, I was not suggesting to change the "Z" in your input data. I was suggesting to replace the "Z" with an "X" in the date formats in your index template.

The following works for me:

PUT _template/tpl
{
  "template": "myindex.*",
  "mappings": {
    "dynamic_date_formats": [
      "basic_ordinal_date_time",
      "yyyy-MM-dd'T'HH:mm:ss.SX",
      "yyyy-MM-dd'T'HH:mm:ss.SSX",
      "yyyy-MM-dd'T'HH:mm:ss.SSSX"
    ]
  }
}

PUT myindex.1/_doc/1
{
  "my_date": "2018-12-23T04:48:08.123Z"
}

GET myindex.1/_mapping

Did you delete your index before you retried indexing your data? Index templates are only applied to new indexes, not to existing indexes.

5

Okay, after a bit more trial and error I found the optimal solution to be:

PUT _template/tpl
{
  "index_patterns": "myidx.*",
  "mappings": {
    "dynamic_date_formats": [
      "yyyy-MM-dd'T'HH:mm:ssX||yyyy-MM-dd'T'HH:mm:ss.SX||yyyy-MM-dd'T'HH:mm:ss.SSX||yyyy-MM-dd'T'HH:mm:ss.SSSX"
    ]
  }
}

Since I have multiple date formats in the same field I needed to use the || operator to allow the field to contain the date in multiple formats.

6

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