topic Re: DB-AFTS with date and datetime in Alfresco Forum

DB-AFTS query with date and datetime

joe_l3 — Fri, 09 Mar 2018 15:16:44 GMT

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 "myM

Re: DB-AFTS with date and datetime

afaust — Fri, 09 Mar 2018 16:15:04 GMT

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.

Re: DB-AFTS with date and datetime

joe_l3 — Fri, 09 Mar 2018 17:36:33 GMT

‌

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.