Date range query with travel-sample date format

Full Text Search
1

Hi, I am trying to build an index for the hotel docs in travel-sample dataset, specifically for the field:
reviews.date

The date format appears to be “YYYY-MM-DD hh-mm-ss zzz” yet I cannot get a result with the date range below.
I thought at first it just wasn’t indexing, but when I update the field to include only the date portion (substr(date,0,10)) it works fine even with a default index and the following query.

Can anyone confirm the same behaviour? Is it an issue with date parser?

Thanks!

{
  "explain": false,
  "fields": [
    "*"
  ],
  "highlight": {},
  "query": { 
    "start": "2010-10-12",
    "end": "2021-10-09",
    "inclusive_start": true,
    "inclusive_end": true
  }
}
2

For anyone else hitting datetime parsing issues in the hotel records of travel-sample, you can run this query to grab just the date (not time or timezone) portion there:
UPDATE travel-sample AS ts
SET r.date = SUBSTR(r.date,0,10) FOR r IN ts.reviews END

3

Hello @tylermitchell,

The problem here is with the datetime parser used. By default FTS expects dates to be in the format specified by RFC-3339, which is a specific profile of ISO-8601.

If you specify a custom datetime analyser with a custom layout like below which fits the actual format in the reviews.date field (“2014-04-11 09:10:19 +0300”), it should work.

"mapping": {
      "analysis": {
        "date_time_parsers": {
          "customDate": {
            "layouts": [
              "2006-01-02 15:04:05 +0300"
            ],
            "type": "flexiblego"
          }
        }
      },

Attaching a screen shot too.

Screenshot 2020-08-03 at 7.12.28 PM
Screenshot 2020-08-03 at 7.12.28 PM1002×826 29.3 KB

Cheers!
Sreekanth

4

Thanks Sreekanth, I really want to use that layout feature but haven’t got it to work yet.

Would you need to specify date formatting characters in the layout?
e.g.
YYYY-MM-DD HH:mm:ss Z ?

And what character would Go expect for the timezone offset?

Even with both these layouts (yours and mine) in the custom parser, I can’t get it to work against the default travel-sample hotel reviews.date object.

I’d love to know if it works for you :slight_smile:
Good to see you!
Tyler

5

Hey,

Please try the below index definition, it would work for travel-sample’s reviews.date queries.

{
  "type": "fulltext-index",
  "name": "sampleIndex",
  "sourceType": "couchbase",
  "sourceName": "travel-sample",
  "sourceUUID": "TODO",
  "planParams": {
    "maxPartitionsPerPIndex": 1024,
    "indexPartitions": 1
  },
  "params": {
    "doc_config": {
      "docid_prefix_delim": "",
      "docid_regexp": "",
      "mode": "type_field",
      "type_field": "type"
    },
    "mapping": {
      "analysis": {
        "date_time_parsers": {
          "customDate": {
            "layouts": [
              "2006-01-02 15:04:05 +0300"
            ],
            "type": "flexiblego"
          }
        }
      },
      "default_analyzer": "standard",
      "default_datetime_parser": "dateTimeOptional",
      "default_field": "_all",
      "default_mapping": {
        "dynamic": true,
        "enabled": false
      },
      "default_type": "_default",
      "docvalues_dynamic": true,
      "index_dynamic": true,
      "store_dynamic": false,
      "type_field": "_type",
      "types": {
        "hotel": {
          "dynamic": false,
          "enabled": true,
          "properties": {
            "reviews": {
              "dynamic": false,
              "enabled": true,
              "properties": {
                "date": {
                  "dynamic": false,
                  "enabled": true,
                  "fields": [
                    {
                      "date_format": "customDate",
                      "docvalues": true,
                      "include_in_all": true,
                      "include_term_vectors": true,
                      "index": true,
                      "name": "date",
                      "type": "datetime"
                    }
                  ]
                }
              }
            }
          }
        }
      }
    },
    "store": {
      "indexType": "scorch"
    }
  },
  "sourceParams": {}
}
6

Thanks - that did work for me, great!
Now just to figure out what I was doing differently/wrong. Good for now thanks so much!