FTS index for date range with custom date format

I’m trying to create a FTS index to use with a DateRangeQuery (in the .NET SDK) on a field that stores dates in the format ‘yyyy-MM-dd’ (no timestamp) but I’m unable to create a custom date parser using the UI in CB 5.5 and I’ve been unable to find any documentation on how to do this using the REST API. Is there an example somewhere of how to do this?

@mschoch

Can you assist here? We’re hitting a wall on this problem at the last minute before a deadline. We thought we’d be doing a simple range comparison in FTS on a date, but since our field has only the date and not the time component we’re having a hard time.

Thanks,
Brant

Hey Brant,

I’m no longer with Couchbase, so you may get better answers directly from the FTS team, but I would expect parsing a date in this format to be straightforward. The date formats used by FTS and bleve are in the format used by the Go time package. The date format you show here would be configured using the format string:

2006-01-02

This format is pretty common, and one of the ones bleve should recognize automatically. You can see it is one of the formats we try automatically here:

As a next step, it’d be helpful to look at actual mapping and document to reproduce the issue. Also we should check the query as well, the date range queries seem to be tricky to configure sometimes and I wouldn’t be surprised to find issues in the SDK related to this.

Query:
{
“query”: {
“must”: {
“conjuncts”: [
{
“start”: “2019-03-15”,
“inclusive_start”: true,
“end”: “9999-12-31”,
“inclusive_end”: true,
“field”: “expiresOn”
}
]
}
}
}

Documents:
{
“type”: “waiver”,
“keyId”: “waivers-waiver-1-1”,
“expiresOn”: “2019-01-02”,
“service”: “waivers”
}
{
“type”: “waiver”,
“keyId”: “waivers-waiver-1-2”,
“expiresOn”: “2020-01-02”,
“service”: “waivers”
}

Index:
{
“type”: “fulltext-index”,
“name”: “idx_search”,
“sourceType”: “couchbase”,
“sourceName”: “primary”,
“planParams”: {
“maxPartitionsPerPIndex”: 171
},
“params”: {
“doc_config”: {
“docid_prefix_delim”: “”,
“docid_regexp”: “”,
“mode”: “type_field”,
“type_field”: “type”
},
“mapping”: {
“default_analyzer”: “standard”,
“default_datetime_parser”: “dateTimeOptional”,
“default_field”: “_all”,
“default_mapping”: {
“default_analyzer”: “”,
“dynamic”: false,
“enabled”: false
},
“default_type”: “_default”,
“docvalues_dynamic”: true,
“index_dynamic”: true,
“store_dynamic”: false,
“type_field”: “_type”,
“types”: {
“waiver”: {
“default_analyzer”: “standard”,
“dynamic”: false,
“enabled”: true,
“properties”: {
“expiresOn”: {
“enabled”: true,
“dynamic”: false,
“fields”: [
{
“name”: “expiresOn”,
“type”: “datetime”,
“store”: false,
“index”: true,
“include_term_vectors”: true,
“include_in_all”: false
}
]
}
}
}
}
},
“store”: {
“indexType”: “upside_down”,
“kvStoreName”: “mossStore”
}
},
“sourceParams”: {}
}

Response from REST API :
{
“status”: {
“total”: 6,
“failed”: 0,
“successful”: 6
},
“request”: {
“query”: {
“must”: {
“conjuncts”: [
{
“start”: “2019-03-15T00:00:00Z”,
“end”: “9999-12-31T00:00:00Z”,
“inclusive_start”: true,
“inclusive_end”: true,
“field”: “expiresOn”
}
]
}
},
“size”: 10,
“from”: 0,
“highlight”: null,
“fields”: null,
“facets”: null,
“explain”: false,
“sort”: [
“-_score”
],
“includeLocations”: false
},
“hits”: ,
“total_hits”: 0,
“max_score”: 0,
“took”: 226700,
“facets”: null
}

Note that the response from couchbase here has added a timestamp to the start and end dates that was not present in the request sent to the REST API.

Hi Dan,

This issue of not getting a response is due to high value upper bound given here in query=> “end”: “9999-12-31”.
If you give an “end” value, smaller than year 2262 (2261) it should work for you.

(During some date parsing code paths- we try to get the nanoseconds from the date value given.
The Unix time in nanoseconds cannot be represented by an int64 for any date before the year 1678 or after 2262.)

thanks,
Sreekanth

Dan,
Aside, I just noted that you are using the older “upside_down” indexing format for FTS in your index definition.
Since 6.0, we have the newer, smaller and better performing indexing format called “scorch”, and we highly recommend you to try that out.
Cheers!

@sreeks would it make sense to catch this early and return an error to the user?

1 Like

The right analysis by @sreeks.

@Dan_Grim This is a go unix time since limitation with int64.
We’d recommend running your query as:

curl -XPOST -H "Content-type:application/json" http://<username>:<password>@<ip>:8094/api/index/<index_name>/query -d '{"query": {"must": {"conjuncts":[{"start":"2019-03-15", "inclusive_start":true, "field":"expiresOn"}]}}}'

@daschl It makes sense to catch this early and return an error to the user, let me track it with a ticket (https://issues.couchbase.com/browse/MB-33462)