Hello, I need to create a date range query on the definition of a smart folder JSON. Which is the correct syntax to define date range queries?

[sistemascssl]

I'm trying with this syntax or something similar, without success:

            "nodes" : [{
                    "id":"11",
                    "name" : "2017",
                    "description" : "Documentos ejercicio Actual",
                    "search" : {
                        "language" : "fts-alfresco",
                        "query" : "=facp:empresa:'CSSL' and =facp:fechafac:'[2017-01-01 TO 2017-12-31]'"
                    }

[kaynezhang]

Try facp:fechafac:2017-01-01..2017-12-31
About range query please refer to http://docs.alfresco.com/community5.0/concepts/rm-searchsyntax-ranges.html

[sistemascssl]

Thank you Kayne,

I've seen before this document and I've tried with these syntax:

"query" : "=facp:empresa:'CSSL' and =facp:fechafac:'[2017-01-01 TO 2017-12-31]'"

"query" : "=facp:empresa:'CSSL' and =facp:fechafac:[2017-01-01 TO 2017-12-31]"

"query" : "=facp:empresa:'CSSL' and =facp:fechafac:2017-01-01 TO 2017-12-31"

"query" : "=facp:empresa:'CSSL' and =facp:fechafac:'2017-01-01..2017-12-31'"

"query" : "=facp:empresa:'CSSL' and =facp:fechafac:2017-01-01..2017-12-31"

and other similar syntax... but all of them with the same result: when I open the Smart folder appears an error on catalina.out log file, notifying that the query is an invalid date string:

ERROR [solr.core.SolrCore] [http-bio-8443-exec-113] org.apache.solr.common.SolrException: Invalid Date String:'2017-01-01 TO 2017-12-31'

I'm sure that is possible to create a Smart folder with queries based on date ranges, but I cannot find the correct syntax... Even trying all the options proposed by the referenced document.

Other ideas?

Thanks

[angelborroy]

Try including +facp:fechafac instead of =facp:fechafac

[afaust · Accepted solution]

Using the "="-prefix is usually reserved for simple terms to indicate that exact matches are required. It has no place for range checks which inherently are always "unexact" matches. Also, the "+"-prefix is a remnant of the underlying/old Lucene query language. I very much recommend prefering use of the proper "AND"/"OR"/"NOT" keywords over these low-level prefixes - it just makes the query that much more readable. Since the original poster was using "and" in his query, it should be remarked that these operators MUST ALWAYS be included in all-caps, otherwise the "and" is interpreted as a regular search keyword.

As for the date ranges / expressions: There are a couple of ways to express a date - most often you will use an IS8601 expression or any substring of it, but you can also use SOLR 4 DateMath expressions to dynamically calculate the date.

# full ISO8601 expressions
=facp:empresa:'CSSL' AND facp:fechafac:'[2017-01-01T12:23:45:678Z TO 2017-12-31T12:23:45.678Z]'

# ISO8601 prefix substring
=facp:empresa:'CSSL' AND facp:fechafac:'[2017-01-01 TO 2017-12-31]'

# DateMath expression for "this year"-query
# NOW is current time, /YEAR rounds to midnight on New Year's Eve, +1YEAR obviously offsets to beginning of next year (effectively end of this year due to 00:00:00.000 time component)
=facp:empresa:'CSSL' AND facp:fechafac:'[NOW/YEAR TO NOW/YEAR+1YEAR]'‍‍‍‍‍‍‍‍‍

[sistemascssl]

Hello everyone,

Thanks a lot Axel!! Your recommendations are really good. I've achieved the desired behavior using this syntax:

"query" : "=facp:empresa:'CSSL' and facp:fechafac:[NOW/YEAR TO NOW/YEAR+1YEAR]"

I needed to drop the "=" before "facp:fechafac" because Alfresco doesn't like it.

Thanks a lot, now it's working!

Regards