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
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.
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.
Ask for and offer help to other Alfresco Content Services Users and members of the Alfresco team.
Related links:
By using this site, you are agreeing to allow us to collect and use cookies as outlined in Alfresco’s Cookie Statement and Terms of Use (and you have a legitimate interest in Alfresco and our products, authorizing us to contact you in such methods). If you are not ok with these terms, please do not use this website.