Add Default Date Format on Mapping

Mehmet_Gencol · November 16, 2021, 2:42pm

I have a mapping that dynamically detects date and stores. Fields are dynamically created, and I don't have any configuration about date formatting. so dates are detected and stored as their formats. They are generally stored as strict_date_optional_time

However, If I receive date with different format than default, such as yyyy/MM/dd, strict_date_optional_time is not set as the format of dynamically created field. In fact it is stored as yyyy/MM/dd. I can then get that field and apply some filters on it.

My problem is that I have a general filtering that creates date query with the format of yyyy-MM-dd. In this case, fields that have different formats (yyyy/MM/dd in our example) are filtered out.

Here is sample data to explain my case clearly.

PUT /index-002

POST /index-002/_doc
{
  "date": "2021/01/01"
}

GET index-002/_search
{
  "query": {
    "query_string": {
      "query": "date:[2021/01/01 TO *]"
    }
  }
}
Result:
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "index-002",
        "_type" : "_doc",
        "_id" : "ve4xKX0Bg0tsDHlcqLOo",
        "_score" : 1.0,
        "_source" : {
          "date" : "2021/01/01"
        }
      }
    ]
  }
}

GET index-002/_search
{
  "query": {
    "query_string": {
      "query": "date:[2021-01-01 TO *]"
    }
  }
}
Result:
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

stephenb · November 16, 2021, 4:22pm

Hi @Mehmet_Gencol Welcome to the community!

Before we try to answer can you share your mapping as well?

Also you will probably get a better results with using a DSL query not a query string like a Range Filter. See Here

stephenb · November 16, 2021, 4:46pm

Try This just add the expected date formats, you can add many...

DELETE index-002

PUT index-002
{
  "mappings": {
    "dynamic_date_formats": ["yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||epoch_millis"]
  }
}

POST /index-002/_doc
{
  "date": "2021/01/01"
}


GET index-002/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2021/01/01",
        "lte": "now"
      }
    }
  }
}

GET index-002/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2021-01-01",
        "lte": "now"
      }
    }
  }
}

Mehmet_Gencol · November 16, 2021, 10:39pm

Hi @stephenb Thank you!

I will think over about not using query string filtering.

Unfortunately, I don't have a fixed mapping. Fields are dynamically created. So I can't decide the mapping. For example I may have a case such that

POST /index-002/_doc
{
  "date": "2021/01/01"
}

POST /index-003/_doc
{
  "date": "2021-01-01"
}

But I need to be able to query both indices with

GET index-*/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2021-01-01",
        "lte": "now"
      }
    }
  }
}

So I may have two cases:

Adding strict_date_optional_time to all possible date fields. Any future field can be queried with default format yyyy-MM-dd. Regardless of the name of field, if I can handle this, it will be amazing for me.
Query date columns regardless of their mapping. Because when I send query above, docs in index-002 will throw the following error. It may also be okay if I solve this error somehow.

failed to parse date field [2021-01-01] with format [yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||epoch_millis]: [failed to parse date field [2021-01-01] with format [yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||epoch_millis]]

stephenb · November 16, 2021, 10:51pm

I am confused ... this is a dynamic mapping ... it will create a flexible date fields for any field that it sees that matches the patterns...

PUT index-002
{
  "mappings": {
    "dynamic_date_formats": ["yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||epoch_millis"]
  }
}

POST /index-002/_doc
{
  "date": "2021/01/01"
}

POST /index-002/_doc
{
  "maybe_a_date": "2021-01-01"
}

flexible date fields

GET index-002

restults 
{
  "index-002" : {
    "aliases" : { },
    "mappings" : {
      "dynamic_date_formats" : [
        "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||epoch_millis"
      ],
      "properties" : {
        "date" : {
          "type" : "date",
          "format" : "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||epoch_millis"
        },
        "maybe_a_date" : {
          "type" : "date",
          "format" : "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||epoch_millis"
        }
      }
    },
    "settings" : {
      "index" : {
        "routing" : {
          "allocation" : {
            "include" : {
              "_tier_preference" : "data_content"
            }
          }
        },
        "number_of_shards" : "1",
        "provided_name" : "index-002",
        "creation_date" : "1637102907427",
        "number_of_replicas" : "1",
        "uuid" : "tBrJs0A2QJq2GXyVjEOL4Q",
        "version" : {
          "created" : "7150299"
        }
      }
    }
  }
}

Your error would be solved using my solution...

Mehmet_Gencol · November 17, 2021, 8:07am

Yes this works but my expectations is to have "dynamic_date_formats": ["strict_date_optional_time||epoch_second"] as default. Here is the case:

PUT /index-001
{
  "mappings": {
    "dynamic_date_formats": ["strict_date_optional_time||epoch_second"]
  }
}

POST /index-001/_doc
{
  "date": "2021/01/01"
}

GET index-001/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2021-01-01",
        "lte": "now"
      }
    }
  }
}
Query Result:
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}
Mapping:
{
  "index-001" : {
    "mappings" : {
      "dynamic_date_formats" : [
        "strict_date_optional_time||epoch_second"
      ],
      "properties" : {
        "date" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        }
      }
    }
  }
}

I want to be able to query dates with yyyy-MM-dd format. Most of the dates we store is already in the yyyy-MM-dd (strict_date_optional_time) format. But we may have some cases that the date field is formatted with some other types.

stephenb · November 17, 2021, 2:29pm

ff your requirement / expectation is to only define those two formats in the dynamic template (which I don't understand why because you could be much more flexible by adding multiple patterns which many do for just this case) then you will need to match / convert those unmatched formats into one of the matched formats when you query there's no automatic way to do that As far as I know.

Or to put in a more simple way

You can only search by the formats that you allow to store the date.

For Example, So if you only accept the 2 formats to store the date you can only search by those formats because it uses the same analyzer when it stores the date as when it searches the date.

That does not mean that you have to search by the exact format that you originally stored the date just by one of the formats that you store the date.

So suppose you store the date with three different formats supported and you used format one when you stored the date You could use any of the three formats to search the date but you can not search with a format that is not one of the three defined formats.

This is my understanding.

Mehmet_Gencol · November 18, 2021, 9:31am

I think I could not express my need. I do not want to restrict my formats. Alongside date formats can be any of them, I want all my date fields should contain a date format that I can manage. Because in my automated query, I want to be able to use specific date formatting. So I may want to have this:

PUT /index-001
POST /index-001
{
    "field1": "2021/01/01"
}

PUT /index-002
POST /index-002
{
    "field1": "2021-01-01"
}

PUT /index-003
POST /index-003
{
    "field1": 1609462800
}

PUT /index-004
POST /index-004
{
    "field1": "20210101"
}

And at the end, I need the following query will work for all indices.

GET index-*/_search
{
  "query": {
    "range": {
      "date": {
        "gte": "2021-01-01",
        "lte": "now"
      }
    }
  }
}

To make it clear, I can use any date format in my query. If I can manage all date fields to contain one certain format, I can use 2021-01-01 as 2021/01/01 or 20210101 or 1609462800 or anything else.

stephenb · November 18, 2021, 3:32pm

Apologies that we are struggling to be clear.

I believe this satisfies your requirement all types ingest all types searched

Note: You need to be aware of timezones... without any these will all be considered UTC see here

DELETE _template/my-template_1

PUT _index_template/template_1
{
  "index_patterns": [
    "index-*"
  ],
  "template": {
    "settings": {
      "number_of_shards": 1
    },
    "mappings": {
      "_source": {
        "enabled": true
      },
      "properties": {
        "desc": {
          "type": "keyword"
        },
        "my_date_fields": {
          "type": "date",
          "format": "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||yyyyMMdd||epoch_millis"
        }
      }
    }
  }
}

DELETE index-*

POST /index-001/_doc
{
    "desc" : "index-1",
    "my_date_fields": "2021/01/01"
}

POST /index-002/_doc
{
     "desc" : "index-2",
    "my_date_fields": "2021-01-01"
}

POST /index-003/_doc
{
    "desc" : "index-3",
    "my_date_fields": 1609459200000
}

POST /index-004/_doc
{
    "desc" : "index-4",
    "my_date_fields": "20210101"
}

GET index-*/_search
{
  "query": {
    "range": {
      "my_date_fields": {
        "gte": "2021/01/01",
        "lte": "now"
      }
    }
  }
}

GET index-*/_search
{
  "query": {
    "range": {
      "my_date_fields": {
        "gte": "2021-01-01",
        "lte": "now"
      }
    }
  }
}

GET index-*/_search
{
  "query": {
    "range": {
      "my_date_fields": {
        "gte": "20210101",
        "lte": "now"
      }
    }
  }
}

GET index-*/_search
{
  "query": {
    "range": {
      "my_date_fields": {
        "gte": 1609459200000,
        "lte": "now"
      }
    }
  }
}

Mehmet_Gencol · November 18, 2021, 3:53pm

You are responding great, thank you for that. And there is no need for any kind of apology. In fact, I am sorry for making my explanations hard to observe.

This solution may make most of my work, yes. However:

I have one big problem: field name is not known at the beginning. it can be my_date_fields or some_other_field_name or etc.
I don't want to restrict date formats:

will bound my possible date fields. I only wish to have strict_date_optional_time in all of my date fields.

stephenb · November 18, 2021, 4:31pm

Hi @Mehmet_Gencol I suggest you read the docs carefully.

Then you will need to only pass in that... and search that

I think you do not perhaps understand how elastisearch stores dates... internally all dates are stored exactly the same this all about being ables to pass in and search the dates with different formats no matter What Format you Pass In ... the date is ALWAYS stored the same in elastic!

Internally, dates are converted to UTC (if the time-zone is specified) and stored as a long number representing milliseconds-since-the-epoch.

If you do not know the name of the fields you will need to use Dynamic Date Detection as you stated Which is discussed here ...

and what I showed above here BUT there is an issue with that

The issue with that is the long millis that will be detected as a number not a date.... that is why an explicit mapping will work better and you generally will not want to turn of numeric date detection.

If you know your date fields have a naming convention you can try dynamic_templates and match on a name

You have lots of options...I am sure you will figure one out that works

Mehmet_Gencol · November 18, 2021, 5:19pm

Actually I read all and could not find a way to solve my problem. To repeat, without restricting date formats by defining dynamic_date_format or some other ways, I want all my date fields to have strict_date_optional_time formatting. So that I can query them in the yyyy-MM-dd format. I will go with this solution:

PUT index-001
{
  "mappings": {
    # Maybe some other types to. I should consider all possible date formats here.
    "dynamic_date_formats": ["yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||strict_date_optional_time||epoch_millis||epoch_second"]
  }
}

This can be a solution but not my expectation. Thank you for your help anyway.

stephenb · November 18, 2021, 5:45pm

There is no magic.. if you want ALL your dates to be strict_date_optional_time then you / your app will need to format / convert them all to strict_date_optional_time before inserting and searching you can not have it both ways.

Your requirement can not be met as you write it as far as I know, perhaps someone else can help.

system · December 16, 2021, 5:45pm

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