Date format with timezone offset

[Xavier_Romero]

Hi there,

Having a hard time to parse a date with zone offset. I need to parse dates like this: "2020-05-07 13:16:44+00:00"

So far, I've tried:

PUT /_template/test
{
 	"index_patterns": ["test"],
	"mappings": {
 	  "properties": {
 			"date1": { "type": "date", "format": "yyyy-MM-dd HH:mm:ssZ" },
 			"date2": { "type": "date", "format": "yyyy-MM-dd HH:mm:ssZZ" },
 			"date3": { "type": "date", "format": "yyyy-MM-dd HH:mm:ssZZZ" },
 			"date4": { "type": "date", "format": "yyyy-MM-dd HH:mm:ssZZZZ" }
 		}
 	}
 }

PUT /test/_doc/1
{  "date1": "2020-05-07 13:16:44+00:00" }

PUT /test/_doc/1
{  "date2": "2020-05-07 13:16:44+00:00" }

PUT /test/_doc/1
{  "date3": "2020-05-07 13:16:44+00:00" }

PUT /test/_doc/1
{  "date4": "2020-05-07 13:16:44+00:00" }

Every attempt fails because the date can't be parsed (example error for the first case, all looks the same).

{
  "error" : {
"root_cause" : [
  {
    "type" : "mapper_parsing_exception",
    "reason" : "failed to parse field [date1] of type [date] in document with id '1'. Preview of field's value: '2020-05-07 13:16:44+00:00'"
  }
],
"type" : "mapper_parsing_exception",
"reason" : "failed to parse field [date1] of type [date] in document with id '1'. Preview of field's value: '2020-05-07 13:16:44+00:00'",
"caused_by" : {
  "type" : "illegal_argument_exception",
  "reason" : "failed to parse date field [2020-05-07 13:16:44+00:00] with format [yyyy-MM-dd HH:mm:ssZ]",
  "caused_by" : {
    "type" : "date_time_parse_exception",
    "reason" : "Text '2020-05-07 13:16:44+00:00' could not be parsed at index 19"
  }
}
  },
  "status" : 400
}

.....

Please, which is the right format I need to use?

Thank you very much.

[cbuescher (Christoph)]

Assuming this is on some late 7.x version, according to the java the docs that we link from our format docs, five is the magic number in this case:

Offset Z : This formats the offset based on the number of pattern letters. One, two or three letters outputs the hour and minute, without a colon, such as '+0130'. The output will be '+0000' when the offset is zero. Four letters outputs the full form of localized offset, equivalent to four letters of Offset-O. The output will be the corresponding localized offset text if the offset is zero. Five letters outputs the hour, minute, with optional second if non-zero, with colon. It outputs 'Z' if the offset is zero. Six or more letters throws IllegalArgumentException .

This at least works for me on 7.6.2

DELETE test

PUT /_template/test
{
 	"index_patterns": ["test"],
	"mappings": {
 	  "properties": {
 			"date": { "type": "date", "format": "yyyy-MM-dd HH:mm:ssZZZZZ" }
 		}
 	}
 }
 
PUT /test/_doc/1
{  "date1": "2020-05-07 13:16:44+00:00" }

GET /test/_search
{
  "docvalue_fields": ["date"]
})

Formatting is also often weird to me, I keep re-reading those docs and need to try things every time, and zone offsets are especially hard. Hope this helps.

[Xavier_Romero]

Great @cbuescher, that worked as expected! As you said, really confusing, since somewhere I've documentation where my exact example was supposed to work with ZZ, somewhere else with ZZZZ...

Thank you very much.

[cbuescher (Christoph)]

With these things its always important to know the exact ES version, since we switched dates from Joda-time to Java time in version 7 and although the format syntax is mostly the same, there are important differences. See https://www.elastic.co/guide/en/elasticsearch/reference/7.x/breaking-changes-7.0.html#breaking_70_java_time_changes as an introduction.