CODEWITHSUNDEEP
xmldatexqueryexist-db
Rob
Rob

Reputation: 15982

XQuery : Fastest way to check dates

According to the fact I have this XML file :

<entries>
  <entry date="2012-10-09T12:09:09">...</entry>
  <entry date="2012-10-09T14:19:23">...</entry>
  ...
  <entry date="2012-10-13T00:00:00">...</entry>
</entries>

And $dateBegin := '2012-10-09T13:00:00' and $dateEnd := '2012-10-12T00:00:00'. I'm looking for an efficient way of getting the entries ranged between $dateBegin and $dateEnd, any idea?

Upvotes: 1

Views: 503

Answers (3)

hutchkintoot
hutchkintoot

Reputation: 1

Do you have a range-index on the entry/@date attribute defined for the collection? If not i believe that would speed up the query.

Upvotes: 0

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243459

Using the confirmed by Rob (the OP) fact, that the entry elements appear sorted in the XML document, this might be faster:

for $dateBegin in xs:dateTime('2012-10-09T13:00:00'),
    $dateEnd in xs:dateTime('2012-10-12T00:00:00')
    return
            /*/*[xs:dateTime(@date) ge $dateBegin
               and
                . << /*/*[xs:dateTime(@date) gt $dateEnd][1]
                ]

When this XQuery is evaluated against the provided XML document:

<entries>
  <entry date="2012-10-09T12:09:09">...</entry>
  <entry date="2012-10-09T14:19:23">...</entry>
  ...
  <entry date="2012-10-13T00:00:00">...</entry>
</entries>

the wanted, correct result is produced:

<entry date="2012-10-09T14:19:23">...</entry>

Upvotes: 1

Rob
Rob

Reputation: 15982

Ok so finally I realized that let $entries := //entry[(xs:dateTime(./@date/string()) ge $dateBegin) and (xs:dateTime(./@date/string()) le $dateEnd)] works. Still wondering if a better solution exists though.

Upvotes: 0

Related Questions