XQuery/Time Based Queries

Motivation

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

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.

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.

References

The following page has instructions on indexing:

http://exist-db.org/exist/apps/doc/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://exist-db.org/exist/apps/doc/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:

xmldb:created

xmldb:last-modified

Other Resources

Timeline mashups in this Wikibook
Gantt Chart using JQuery
Timeline SVG reports
Gantt Charts from XML data using Anychart