CODEWITHSUNDEEP
phpdatetimesearchelasticsearchnullable
marked-down
marked-down

Reputation: 10408

Storing a datetime with optional parameters in an elasticsearch index?

So, I'm developing an application where I need a search tool that will index photos and documents - but sometimes I won't know the precise month or day that a document originated at, so up until now, I have been storing dates in my MySQL table as a date or datetime field which allows optional values:

2015-03-11 // Valid MySQL date
2015-12-00 // Also a valid MySQL date, but a null day to represent 'it is not known'
2015-00-00 // Another valid MySQL date, with a null date and month to represent 'this document came from the year 2015, but at some unknown point'

Now, this actually works quite well, MySQL will still order and sort the dates in an appropriate manner.

However, now I come to building the search tool using Elasticsearch, and it clearly isn't happy with this syntax. Attempting to index a document with a datetime with 'out of range' values (such as 2015-00-00) results in this response:

{
    "error":"MapperParsingException[failed to parse [originated_at]]; nested: MapperParsingException[failed to parse date field [2015-00-00], tried both date format [dateOptionalTime], and timestamp number with locale []]; nested: IllegalFieldValueException[Cannot parse \"2015-00-00\": Value 0 for monthOfYear must be in the range [1,12]]; ",
    "status":400
}

For obvious reasons. However, optional dates are a requirement and I need my chosen solution to allow this.

What is the best way to get Elasticsearch to allow me to store optional date properties and still be able to order and search by dates?

Upvotes: 2

Views: 1424

Answers (2)

Oleksandr Pyrohov
Oleksandr Pyrohov

Reputation: 16216

Setup a mapping:

{
    "yourIndex": {
        "properties": {
            "date": {
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss"
            }
        }
    }
}

Also, look here - Elasticsearch mapping date format.

Upvotes: 1

Val
Val

Reputation: 217274

In your mapping, you can declare your date field with several different formats separated with ||, like this:

{
    "date_field": {
        "type": "date",
        "format": "yyyy||yyyy-MM||yyyy-MM-dd"
    }
}

As a result, you'll be able to store any of the following dates:

  • 2015 (i.e. yyyy)
  • 2015-12 (i.e. yyyy-MM)
  • 2015-12-01 (i.e. yyyy-MM-dd)

Try it out.

Upvotes: 1

Related Questions