Search content by dates in alfresco using CMIS API

Hi all!

I have a new project that use Alfresco as a backend to store content. I need select content by date. User specifies dates and the service should find and display it. I've chosen CMIS API. It supports query language like SQL to get content. But I've got a problem. I need to specify the date in my query, that looks like this:

view plainprint?

1. SELECT * FROM my:content WHERE my:modifyDate < 01.01.2010  

SELECT * FROM my:content WHERE my:modifyDate < 01.01.2010

My problem is a date format. In alfresco wiki, I found this description:

datetime literals Based on SQL-92 with 'T' replacing in , extended to include milli-seconds, and modified to include 'Z'. TIMESTAMP '2010-04-01T12:15:00.000Z' TIMESTAMP '2010-04-01T12:15:00.000+01:00' TIMESTAMP '2010-04-01T12:15:00.000-02:00' In Java SimpleDateFormat terms: yyyy-MM-dd'T'HH.mm.ss.SSS with the as Z, +hh:mm, or -hh:mm which does not quite match up :-)

But it doesn't work. Why???!!! Because I had simply copy-pasted the format for SimpleDateFormat: yyyy-MM-dd'T'HH.mm.ss.SSS. Next hour I could not get why it doesn't work. That format is wrong and must be: yyyy-MM-dd'T'HH:mm:ss.SSS'Z' Also, in alfresco SVN and I've found a good helper class for this: org.alfresco.util.CachingDateFormat. But I didn't hav it in my build of alfresco(I use community build version 3.3, but it was build early). When I upgrade, I will use this class, but for now I wrote simple type helper that looks like this:

view plainprint?

1. package com.blogspot.jajatips.alfresco.util;  
2.   
3. import org.apache.commons.lang.time.FastDateFormat;  
4. import java.util.Date;  
5.   
6. /** 
7.  * Date: 03.08.2010 
8.  * @author Dmitry N. Pokidov 
9.  */  
10. public class TypeHelper {  
11.     public static String formatDate(Date date) {  
12.         //TODO: CachingDateFormat.getCmisSqlDatetimeFormat()  
13.         FastDateFormat cmisDateFormat = FastDateFormat.getInstance("yyyy-MM-dd'T'HH:mm:ss.SSSZZ");  
14.         return new StringBuilder("TIMESTAMP '").append(cmisDateFormat.format(date)).append("'").toString();  
15.     }  
16. }  

package com.blogspot.jajatips.alfresco.util;

import org.apache.commons.lang.time.FastDateFormat;
import java.util.Date;

/**
 * Date: 03.08.2010
 * @author Dmitry N. Pokidov
 */
public class TypeHelper {
    public static String formatDate(Date date) {
        //TODO: CachingDateFormat.getCmisSqlDatetimeFormat()
        FastDateFormat cmisDateFormat = FastDateFormat.getInstance("yyyy-MM-dd'T'HH:mm:ss.SSSZZ");
        return new StringBuilder("TIMESTAMP '").append(cmisDateFormat.format(date)).append("'").toString();
    }
}

As you can see, I use FastDateFormat from common-lang, that cache date formats. Also, their formats are thread safe, which is very important if you have some static queries.

Well, I'm going to request to make changes in SimpleDateFormat in alfresco wiki, so no one would have this trouble :)

UPD: Good news, I have fixed alfresco wiki page :)