DB-AFTS query with date and datetime

[joe_l3]

Has anyone ever tried db-afts with date or datetime ? It seems iso dates are stored as pure plain text into the database causing inconsistent results in transactional query.
I guess I missing something else but I was unable to get valid results.

Content stored in Alfresco 5.2.g with a custom date "myModel:date" (type d:date)

## Mysql DB shows this date
2018-03-07T10:06:38.458Z

Observed results using db-afts via Share node browser

## Exact date without time
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07
expected at least 1 - results 0

## Exact date with Zulu time
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07T10:06:38.458Z
expected 1 - results 1

## Exact date with time offset
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07T09:06:38+01:00
expected 1 - returns error, no viable alternative at character ':'

## Exact date with time offset and quotation marks 
+TYPE:"myModel:invoice" AND =myModel:date:"2018-03-07T09:06:38+01:00"
expected 1 - results 0

## Date and wildcard 
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07*
expected at least 1 - results 2

## Date and wildcard after hours
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07T10*
expected at least 1 - results 2

## Date and wildcard after minutes
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07T10:06*
expected 1 - returns error, analysis mode not supported for DB DEFAULT

## Date and wildcard after minutes with quotation marks 
+TYPE:"myModel:invoice" AND =myModel:date:"2018-03-07T10:06*"
expected 1 - results 0

## Date and wildcard after minutes (escaped)
+TYPE:"myModel:invoice" AND =myModel:date:2018-03-07T10\:06*
expected 1 - results 1

[afaust]

Date FTS queries are not fully supported against the DB. Various features of the date support rely on index-specific handling. There are is no partial match support, so just selecting by the date without time does not work and is expected as such. In the example with the different timezone you are also missing the milliseconds, so even with timezone conversion (which I doubt would work) you'll always have a mismatch there.

I would recommend to never rely on DB FTS for date queries unless you are working with exact matches. I prefer using CMIS QL against the DB for any date queries, especially if I need to perform some "between X and Y" selection logic, since CMIS QL offers better range and timestamp support.

[joe_l3]

‌

thanks for your help.
I suspected date queries were not fully supported with db fts. The curious thing seems that dates are handled as pure plain text. As you can see in the last examples, I use the wildcard to search for a date object like a string. I even had to escape the semicolon for the time part.
Anyway I used db fts only to see how it works with date and datetime objects.
For the transactional queries I usually use db cmis to search for exact date or date ranges but sometime it's useful even to have an alternative solution.