Lucene TermRangeQuery for DateTimes

[Skrypt]

https://stackoverflow.com/questions/19655493/how-to-convert-a-lucene-net-rangequery-to-a-numericrangequery-for-date-range-sea

As explained here when we index a DateTime in Lucene it is stored as a string value.
To be able to filter the indexes with a DateTime range then we should use a TermRangeQuery instead of a NumericRangeQuery. Right now the RangeQueryProvider uses the TermRangeQuery only when the date evaluated is sent as a string to the RangeQueryProvider.

RangeQueryProvider.cs

    case JTokenType.String:
         var minString = gt?.Value<string>();
         var maxString = lt?.Value<string>();
         rangeQuery = TermRangeQuery.NewStringRange(field, minString, maxString, includeLower, includeUpper);
         break;

We need to document that fact. So for retrieving a range of DateTime we should use for example :

{
  "query": {
    "range": { "Content.ContentItem.CreatedUtc": { "lt": "{{ 'now' | date: '%Y%m%d%H%M%S'}}" }}
  }
}

and not

{
  "query": {
    "range": { "Content.ContentItem.CreatedUtc": { "lt": {{ 'now' | date: '%Y%m%d%H%M%S'}} }}
  }
}

Datetime format in Liquid reference :
https://help.looker.com/hc/en-us/articles/360023800253-Easy-Date-Formatting-with-Liquid

Examples :

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "Content.ContentItem.ContentType": "Workshop"
          }
        },
        {
          "range": {
            "Workshop.Date": {
              "lte": "{{ 'now' | date: '%Y%m%d%H%M%S'}}"
            }
          }
        }
      ]
    }
  },
  "sort": {
    "Workshop.Date": {
      "order": "desc"
    }
  },
  "size": 3
}

Also working but non valid JSON :

{
  "query": {
    "bool": {
      "must": {
        "term": {
          "Content.ContentItem.ContentType": "Workshop"
        }
      }
    },
    "bool": {
      "must": {
        "range": {
          "Workshop.Date": {
            "lte": "{{ 'now' | date: '%Y%m%d%H%M%S'}}"
          }
        }
      }
    }
  },
  "sort": {
    "Workshop.Date": {
      "order": "desc"
    }
  },
  "size": 3
}

[sebastienros]

Very nice

[PiemP]

Hi @Skrypt,

I hope this is the right place where publish this info. I have make some test over the query with the range clause. If I have a date field on my content item and if I mark this field as Analyzed the range query doesn't work. Probably is not necessary because, the description of the Analyzed checkbox talk about the conversion of the field value to text, but I report it and you decide how to do with this case.

Another case is, if exist a content item with NULL value in the date field, this content item is returned only in "gt" (greater than) or "gte" (greater than or equals) compare. To have content item with NULL value in the date field, in case of "lt" or "lte" compare you must create an "or" query like this:

{
    "query": {
        "bool":{
            "must":{
                "term": {
                    "Content.ContentItem.ContentType": "News"
                }
            }
        },
        "bool": {
            "should": [
                {
                    "bool":{
                        "must":{
                            "range": {
                                    "News.datadiscadenza": {
                                        "lte": "{{ " now " | date: "%Y%m%d%H%M"}}"
                                    }
                            }
                        }
                    }
                },
                {
                    "bool":{
                        "must":{
                            "term": {
                                "News.datadiscadenza": "NULL"
                            }
                        }
                    }
                }
            ]
        }
    },
    "sort": {
        "News.datadipubblicazione": {
            "order": "desc"
        }
    },
    "size": 3
}

[Skrypt]

Ok normallly you need to "Store" this value in the index instead of "Analyze" because analyze will tokenize the words contained in the string passed to Lucene. Analyze is used for full text search scenarios whereas what you want is to find a "fixed" value. For example we "Store" a ContentItemId. I will take a look at why there is a difference between the lt,gt vs lte,gte. It is normal though that we index a "NULL" string value if nothing is entered in this field but maybe for a DateTime, Date field we should index a fixed value of "0" since we know that this is going to be a numeric string value. We should probably do the same with a NumericField then too. Though "0" is not "NULL" so, maybe what we need is the ability to set a default value on a DateTime, Numeric field to make sure there is an actual numeric value provided.

What you did is fine for now and maybe that's the solution too. With ElasticSearch there is also an EXIST Query which is kind of equivalent of what you did. The idea is that you want to also get the items that are "NULL" but the TermRangeQueryProvider by default doesn't return these values as it can't "convert" it to a numeric value to evaluate and filter on.