<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<section id="JCR.DatePropertyComparison">
  <title>Date Property Comparison</title>

  <para>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.</para>

  <section id="JCR.DatePropertyComparison.RepositoryStructure">
    <title>Repository Structure</title>

    <para>Repository contains nt:resource nodes with different values of
    jcr:lastModified property</para>

    <itemizedlist>
      <listitem>
        <para>root</para>

        <itemizedlist>
          <listitem>
            <para>document1 (nt:file)</para>

            <itemizedlist>
              <listitem>
                <para>jcr:content (nt:resource)
                jcr:lastModified="2006-01-19T15:34:15.917+02:00"</para>
              </listitem>
            </itemizedlist>
          </listitem>

          <listitem>
            <para>document2 (nt:file)</para>

            <itemizedlist>
              <listitem>
                <para>jcr:content (nt:resource)
                jcr:lastModified="2005-01-19T15:34:15.917+02:00"</para>
              </listitem>
            </itemizedlist>
          </listitem>

          <listitem>
            <para>document3 (nt:file)</para>

            <itemizedlist>
              <listitem>
                <para>jcr:content (nt:resource)
                jcr:lastModified="2007-01-19T15:34:15.917+02:00"</para>
              </listitem>
            </itemizedlist>
          </listitem>
        </itemizedlist>
      </listitem>
    </itemizedlist>
  </section>

  <section id="JCR.DatePropertyComparison.QueryExecution">
    <title>Query Execution</title>

    <para><emphasis role="bold">SQL</emphasis></para>

    <para>In SQL you have to use the keyword <emphasis
    role="bold">TIMESTAMP</emphasis> 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 ( <ulink
    url="http://en.wikipedia.org/wiki/ISO_8601">http://en.wikipedia.org/wiki/ISO_8601</ulink>
    and well explained in a W3C note <ulink
    url="http://www.w3.org/TR/NOTE-datetime">http://www.w3.org/TR/NOTE-datetime</ulink>).</para>

    <para>You will see that it can be a date only (YYYY-MM-DD) but also a
    complete date and time with a timezone designator (TZD).</para>

    <programlisting language="java">// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
StringBuffer sb = new StringBuffer();
sb.append("select * from nt:resource where ");
sb.append("( jcr:lastModified &gt;= TIMESTAMP '");
sb.append("2006-06-04T15:34:15.917+02:00");
sb.append("' )");
sb.append(" and ");
sb.append("( jcr:lastModified &lt;= 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();</programlisting>

    <para><emphasis role="bold">XPath</emphasis></para>

    <para>Compared to the SQL format, you have to use the keyword <emphasis
    role="bold">xs:dateTime</emphasis> and surround the datetime by extra
    brackets: xs:dateTime('datetime'). The actual format of the datetime also
    conforms with the ISO date standard.</para>

    <programlisting language="java">// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
StringBuffer sb = new StringBuffer();
sb.append("//element(*,nt:resource)");
sb.append("[");
sb.append("@jcr:lastModified &gt;= xs:dateTime('2006-08-19T10:11:38.281+02:00')");
sb.append(" and ");
sb.append("@jcr:lastModified &lt;= 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();</programlisting>
  </section>

  <section id="JCR.DatePropertyComparison.FetchingResult">
    <title>Fetching the result</title>

    <para>Let's get nodes:</para>

    <programlisting language="java">NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node foundNode = it.nextNode();
}</programlisting>

    <para>NodeIterator will return "/document3/jcr:content".</para>

    <para>We can also get a table:</para>

    <programlisting language="java">String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}</programlisting>

    <para>The table content is: <table>
        <title>Table content</title>

        <tgroup cols="3">
          <thead>
            <row>
              <entry>jcr:lastModified</entry>

              <entry>...</entry>

              <entry>jcr:path</entry>
            </row>
          </thead>

          <tbody>
            <row>
              <entry>2007-01-19T15:34:15.917+02:00</entry>

              <entry>...</entry>

              <entry>/document3/jcr:content</entry>
            </row>
          </tbody>
        </tgroup>
      </table></para>
  </section>
</section>
