What's in a date?

[andy1]

A quick tour of Alfresco support to query date and date time properties.



By default, Alfresco treats date and datetime properties the same. Both are indexed and queried to a resolution of day. The index actually stores the date string as yyyy-MM-dd, for example, 2011-02-01, to support both querying and ordering down to day. This approach has several limitations. While the extra resolution is currently required to be included at query time it is ignored. Date properties can be ordered at query time. Datetime properties are ordered after the query execution in Java, requiring a DB property fetch to get the missing time.



However, datetime properties can be configured to use the alternative DateTimeAnalyser supporting full time resolution, down to milliseconds. This configuration also supports variable resolution of dates. If you just include year in a query against a datetime type it will only consider the year in the match. For example, the following Alfresco FTS queries will match with increasing date resolution when using the DateTimeAnalyser.



@cm:created:'2011'

@cm:created:'2011-02'

@cm:created:'2010-02-01'

@cm:created:'2010-02-01T11'

@cm:created:'2010-02-01T11:04'

@cm:created:'2010-02-01T11:04:31'

@cm:created:'2010-02-01T11:04:31.000'



Similarly, if only years are used in a datetime range it will ignore the lower resolution fields. (Take care to make sure the resolution of the start date and end match as mixed resolutions are not currently supported)



@cm:created:['2010' TO '2011']

@cm:created:['2011-02-01T11:03' TO '2011-02-01T11:04']



The DateTimeAnalyser is not used by default as it would require all Alfresco users to rebuild their lucene indexes. It can be configured (after stopping Alfresco)  by either



1) changing the setting in alfresco/model/dataTypeAnalyzers.properties to:

d_dictionary.datatype.d_datetime.analyzer=org.alfresc...



or, 2) copying alfresco/model/dataTypeAnalyzers.properties and related files to a new location and changing the def...





Once configured to use the DateTimeAnalyser, delete your existing indexes and restart Alfresco with the index.recovery.mode property set to FULL.



index.recovery.mode=FULL



The date time tokeniser stores the dates in parts - as a crude trie of year, month, day, hour (24H), minutes, seconds and milliseconds. As this only supports querying there is an additional field in the index to support ordering.



Varying resolution date time queries and range queries are supported in Alfresco 3.4.0E and later. The DateTimeAnalyser has been around since Alfresco 2.1.