# Add Default Date Format on Mapping

**URL:** <https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341>\
**Category:** Elasticsearch\
**Created:** [November 16, 2021, 2:42pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341 "2021-11-16T14:42:52Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mehmet\_Gencol](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mehmet_gencol/32/97175_2.png) [@Mehmet\_Gencol](https://discuss.elastic.co/u/Mehmet_Gencol)\
**Post date:** [November 16, 2021, 2:42pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/1 "2021-11-16T14:42:52Z")

</div>

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.

```auto
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" : []
  }
}

```

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 16, 2021, 4:22pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/2 "2021-11-16T16:22:40Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-filter-context.html)

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 16, 2021, 4:46pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/3 "2021-11-16T16:46:05Z")

</div>

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

```auto
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"
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Mehmet\_Gencol](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mehmet_gencol/32/97175_2.png) [@Mehmet\_Gencol](https://discuss.elastic.co/u/Mehmet_Gencol)\
**Post date:** [November 16, 2021, 10:39pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/4 "2021-11-16T22:39:32Z")

</div>

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

```auto
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

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

```

So I may have two cases:

1. 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.

2. 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]]

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 16, 2021, 10:51pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/5 "2021-11-16T22:51:40Z")

</div>

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...

```auto
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

```auto
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...

---

<div class="post-metadata">

**Author:** ![Mehmet\_Gencol](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mehmet_gencol/32/97175_2.png) [@Mehmet\_Gencol](https://discuss.elastic.co/u/Mehmet_Gencol)\
**Post date:** [November 17, 2021, 8:07am UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/6 "2021-11-17T08:07:15Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 17, 2021, 2:29pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/7 "2021-11-17T14:29:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mehmet\_Gencol](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mehmet_gencol/32/97175_2.png) [@Mehmet\_Gencol](https://discuss.elastic.co/u/Mehmet_Gencol)\
**Post date:** [November 18, 2021, 9:31am UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/8 "2021-11-18T09:31:16Z")

</div>

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:

```auto
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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 18, 2021, 3:32pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/9 "2021-11-18T15:32:38Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html)

```auto
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"
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Mehmet\_Gencol](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mehmet_gencol/32/97175_2.png) [@Mehmet\_Gencol](https://discuss.elastic.co/u/Mehmet_Gencol)\
**Post date:** [November 18, 2021, 3:53pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/10 "2021-11-18T15:53:08Z")

</div>

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:

1. 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.

2. I don't want to restrict date formats:

> [@stephenb](#):
>
> ```auto
> "yyyy/MM/dd HH:mm:ss||yyyy/MM/dd||yyyy-MM-dd||yyyyMMdd||epoch_millis"
> 
> ```

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

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 18, 2021, 4:31pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/11 "2021-11-18T16:31:27Z")

</div>

Hi @Mehmet_Gencol I suggest you read the docs carefully.

> [@Mehmet\_Gencol](#):
>
> will bound my possible date fields. I only wish to have `strict_date_optional_time` in all of my date fields.

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.

> **[Date field type | Elasticsearch Guide \[8.11\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html)**

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 ...

> **[Dynamic field mapping | Elasticsearch Guide \[8.11\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/dynamic-field-mapping.html#date-detection)**

and what I showed above [here](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/5) **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

> **[Dynamic templates | Elasticsearch Guide \[8.11\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/dynamic-templates.html)**

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

---

<div class="post-metadata">

**Author:** ![Mehmet\_Gencol](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mehmet_gencol/32/97175_2.png) [@Mehmet\_Gencol](https://discuss.elastic.co/u/Mehmet_Gencol)\
**Post date:** [November 18, 2021, 5:19pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/12 "2021-11-18T17:19:50Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 18, 2021, 5:45pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/13 "2021-11-18T17:45:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 16, 2021, 5:45pm UTC](https://discuss.elastic.co/t/add-default-date-format-on-mapping/289341/14 "2021-12-16T17:45:43Z")

</div>

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