Date Property Comparison
Find all nodes of primary type "nt:resource" whose jcr:lastModified property value is greater than 2006-06-04 and less than 2008-06-04.
Repository Structure
Repository contains nt:resource nodes with different values of jcr:lastModified property
- root
- document1 (nt:file)
- jcr:content (nt:resource) jcr:lastModified="2006-01-19T15:34:15.917+02:00"
- document2 (nt:file)
- jcr:content (nt:resource) jcr:lastModified="2005-01-19T15:34:15.917+02:00"
- document3 (nt:file)
- jcr:content (nt:resource) jcr:lastModified="2007-01-19T15:34:15.917+02:00"
Query Execution
SQL
In SQL you have to use the keyword
TIMESTAMP for date comparisons. Otherwise the date would be interpreted as a string.
The date has to be surrounded by single quotes (
TIMESTAMP 'datetime') and in the ISO standard format: YYYY-MM-DDThh:mm:ss.sTZD (
http://en.wikipedia.org/wiki/ISO_8601 and well explained in a W3C note
http://www.w3.org/TR/NOTE-datetime).
You see it can be a date only (YYYY-MM-DD) but also a complete date and time with a timezone designator (TZD).
// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
StringBuffer sb = new StringBuffer();
sb.append("select * from nt:resource where ");
sb.append("( jcr:lastModified >= TIMESTAMP '");
sb.append("2006-06-04T15:34:15.917+02:00");
sb.append("' )");
sb.append(" and ");
sb.append("( jcr:lastModified <= TIMESTAMP '");
sb.append("2008-06-04T15:34:15.917+02:00");
sb.append("' )");
String sqlStatement = sb.toString();
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();
Compared to the SQL format you have to use the keyword
xs:dateTime and surround the datetime by extra brackets:
xs:dateTime('datetime'). The actual format of the datetime also conforms with the ISO date standard.
// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
StringBuffer sb = new StringBuffer();
sb.append("//element(*,nt:resource)");
sb.append("[");
sb.append("@jcr:lastModified >= xs:dateTime('2006-08-19T10:11:38.281+02:00')");
sb.append(" and ");
sb.append("@jcr:lastModified <= xs:dateTime('2008-06-04T15:34:15.917+02:00')");
sb.append("]");
String xpathStatement = sb.toString();
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();Fetching the result
Let's get nodes:
NodeIterator it = result.getNodes();
if(it.hasNext())
{
Node foundNode = it.nextNode();
}
NodeIterator will return "/document3/jcr:content".
We can also get a table:
String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
Row row = rit.nextRow();
// get values of the row
Value[] values = row.getValues();
}
The table content is:
| jcr:lastModified | ... | jcr:path |
|---|
| 2007-01-19T15:34:15.917+02:00 | ... | /document3/jcr:content |
