# The curse of dynamic dates
**URL:** https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633
**Category:** Elasticsearch
**Created:** [April 19, 2019, 2:14pm UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633 "2019-04-19T14:14:21Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@techplex](https://discuss.elastic.co/u/techplex)
#### Post date: [April 19, 2019, 2:14pm UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633/1 "2019-04-19T14:14:21Z")
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](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#basic_ordinal_date_time)
> `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?
---
### Author:  [@abdon](https://discuss.elastic.co/u/abdon)
#### Post date: [April 19, 2019, 4:50pm UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633/2 "2019-04-19T16:50:30Z")
Elasticsearch 7 [has switched from joda time to java.time](https://www.elastic.co/guide/en/elasticsearch/reference/current/breaking-changes-7.0.html#breaking_70_java_time_changes). 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`:
```auto
{
"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).
---
### Author:  [@techplex](https://discuss.elastic.co/u/techplex)
#### Post date: [April 19, 2019, 8:10pm UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633/3 "2019-04-19T20:10:22Z")
@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](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html) 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"
}
}
}
```
---
### Author:  [@abdon](https://discuss.elastic.co/u/abdon)
#### Post date: [April 20, 2019, 9:53am UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633/4 "2019-04-20T09:53:05Z")
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:
```auto
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.
---
### Author:  [@techplex](https://discuss.elastic.co/u/techplex)
#### Post date: [May 2, 2019, 9:20pm UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633/5 "2019-05-02T21:20:56Z")
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.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 30, 2019, 9:20pm UTC](https://discuss.elastic.co/t/the-curse-of-dynamic-dates/177633/6 "2019-05-30T21:20:58Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.