XQuery/Time Based Queries

      Motivation

      You want to find items in a collection based on date-time information.

      ↑Jump back a section

      Method

      By default, XML files use standard ISO dateTime structures to store temporal information. There are two main XML data types related to storing time:

      xs:date - for storing just the date in YYYY-MM-DD format. xs:dateTime - for storing both the date and time.

      There are many other structures for storing just year, month, day and time etc. but this example will only cover dates and dateTimes.

      ↑Jump back a section

      Sample Event Structure

      In the following example we will store "event" data using a simple date structures. Events will have just a single start date or a date range with a start date and end date:

      <event>
   <id>6</id>
   <name>Architecture Tradeoff Analysis</name>
   <start-date>2011-04-07</start-date>
   <end-date>2011-04-21</end-date>
</event>

      You can find all events that are occurring during any specific point in time with the following xquery structure.

      Source from events-at-time.xq

      {: get a URL parameter to this XQuery :)
let $date := xs:date(request:get-parameter('date', ''))
(: create a sequence of all events :)
let $events := collection('/db/apps/timelines/data')//event
 
return
   {: return all events that start before the date AND end after the date :)
   for $event in $events[
      xs:date(./start-date/text()) lt $date
      and xs:date(./end-date/text()) gt $date
      ]
    return $event

      You can also set up very fast searches based on the date-time structures even for large collections of 100,000 items after you learn how to configure range indexes on XML xs:dateTime structures. See: http://www.w3.org/TR/xmlschema-2/#dateTime how date-time structures work.

      ↑Jump back a section

      References

      The following page has instructions on indexing:

      http://demo.exist-db.org/exist/indexing.xml

      And make sure to read section 2.2 on range indexes.

      I would also use the xs:date and xs:dateTime structures in your range indexes.

      Your collection configuration file (see http://demo.exist-db.org/exist/indexing.xml#idxconf) might have the following lines if you are tracking document creation and modified dateTimes:

      <create qname="start-date" type="xs:date"/> <create qname="end-date" type="xs:date"/> <create qname="created-dateTime" type="xs:dateTime"/> <create qname="last-modified-dateTime" type="xs:dateTime"/>

      Note that all eXist collections and resource also have both these dates in their metadata. You can use the xmdb module to get these timestamps.

      http://demo.exist-db.org/exist/functions/xmldb/created http://demo.exist-db.org/exist/functions/xmldb/last-modified

      ↑Jump back a section

      Other Resources

      ↑Jump back a section
      Last modified on 3 October 2011, at 18:11