AnsweredAssumed Answered

DB-AFTS query with date and datetime

Question asked by joe.l3 on Mar 9, 2018
Latest reply on Mar 9, 2018 by 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

Outcomes