XQuery/Time Comparison with XQuery

Motivation edit

You have two identical lists of items with timestamps. You want to compare the items to see what items are newer.

Method edit

We will write a function that compares the timestamps of the items in two lists.

Sample Data Sets edit

let $list1 :=
<list>
   <item dateTime="2009-06-01T11:59:00.000-05:00">apples</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">bananas</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">carrots</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">eggplant</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">grapes</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">oranges</item>
</list>

let $list2 :=
<list>
   <item dateTime="2009-01-01T11:59:00.000-05:00">apples</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">bananas</item>
   <item dateTime="2009-03-01T11:59:00.000-05:00">carrots</item>
   <item dateTime="2009-02-01T11:58:00.000-05:00">eggplant</item>
   <item dateTime="2009-02-01T12:00:00.000-05:00">grapes</item>
   <item dateTime="2009-04-01T11:59:00.000-05:00">oranges</item>
</list>

Sample XQuery Function edit

declare function local:older($list1 as node()*, $list2 as node()*) as node()* {
for $item1 in $list1/item
   let $item2 := $list2/item[./text() = $item1/text()]
   return
      <div>
       {attribute {'class'} 
           {if ( xs:dateTime($item1/@dateTime) lt xs:dateTime(fn:current-dateTime) )
            then 'older'
            else 'newer'
            }
       }
       {$item1/text()}
      </div>
};

Comparison Screen Image edit

Sample Test Driver edit

<html>
   <head>
   <style language="text/css">
  <![CDATA[
    body {font-family: Ariel,Helvetica,sans-serif; font-size: medium;}
    h2 {padding: 3px; margin: 0px; text-align: center; font-size: large; background-color: silver;}
    .left, .right {border: solid black 1px; padding: 5px;}
    .older {background-color: pink;}
    .left {float: left; width: 390px}
    .right {margin-left: 410px; width: 390px}

  ]]>
  </style>

   </head>
   <body>
      <h1>Older Items on Second List Report</h1>
      <div class="left">
         <h2>List 1</h2>
         {for $item in $list1/item return <div>{$item/@dateTime} dateTime={string(fn:current-dateTime)}</div>}
      </div>
      <div class="right">
         <h2>List 2</h2>
         {for $item in $list2/item return <div>{$item/text()} dateTime={string($item/@dateTime)}</div>}
      </div>
      <br/>
      <p>The pink items are older items.</p>
      <div class="left">
         <h2>Items on 2 Older Then 1</h2>
         {local:older($list1, $list2)}
      </div>
      <div class="right">
         <h2>Items on 1 Older Then 2</h2>
         {local:older($list2, $list1)}
      </div>
   </body>
</html>

Execute

Collating edit

Alternatively, two ordered lists can be collated to derive a set of updates. Here the items are wrapped in a div to carry the added information about the merge. Items in list1 but not list2 are flagged as new, items in list 2 but not list 1 as to be deleted and items which are newer in list 1 than list 2 as newer.

declare function local:merge($a, $b  as node()*)  as node()* {
    if (empty($a) and empty($b))
    then ()
    else  if (empty ($b) or $a[1] lt $b[1])
    then  (<div class="add">{$a[1]}</div>, local:merge(subsequence($a, 2), $b))
    else  if (empty($a) or $a[1] gt $b[1])
    then  (<div class="delete">{$b[1]}</div>,local:merge($a, subsequence($b,2)))          
   else   (<div  class="{ if (xs:dateTime($a[1]/@dateTime) gt xs:dateTime($b[1]/@dateTime))
                          then "newer" 
                          else "older"}"> 
              {$a[1]}
           </div>,
            local:merge(subsequence($a,2), subsequence($b,2))
           )
   };

The sample data and main script are changed slightly:

declare option exist:serialize "method=xhtml media-type=text/html";

let $list1 :=
<list>
   <item dateTime="2009-06-01T11:59:00.000-05:00">apples</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">bananas</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">carrots</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">cabbage</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">eggplant</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">grapes</item>
 </list>
 
let $list2 :=
<list>
   <item dateTime="2009-01-01T11:59:00.000-05:00">apples</item>
   <item dateTime="2009-02-01T11:59:00.000-05:00">bananas</item>
   <item dateTime="2009-03-01T11:59:00.000-05:00">carrots</item>
   <item dateTime="2009-02-01T11:58:00.000-05:00">eggplant</item>
   <item dateTime="2009-02-01T12:00:00.000-05:00">grapes</item>
   <item dateTime="2009-04-01T11:59:00.000-05:00">oranges</item>
</list>

return

<html>
   <head>
   <style language="text/css">
  <![CDATA[
    body {font-family: Ariel,Helvetica,sans-serif; font-size: medium;}
    h2 {padding: 3px; margin: 0px; text-align: center; font-size: large; background-color: silver;}
    .left, .right {border: solid black 1px; padding: 5px;}
    .newer{background-color: lightgreen;}
    .older{background-color: lightred;}
     .delete{background-color: red;}
    .add{background-color: green;}
   .left {float: left; width: 390px}
    .right {margin-left: 410px; width: 390px}
 
  ]]>
  </style>
 
   </head>
   <body>
      <h1>Update Report</h1>
      <div class="left">
         <h2>List 1</h2>
         {for $item in $list1/item return <div>{$item/text()} dateTime={string($item/@dateTime)}</div>}
      </div>
      <div class="right">
         <h2>List 2</h2>
         {for $item in $list2/item return <div>{fn:current-dateTime} dateTime={string(dateTime(current-dateTime))}</div>}
      </div>
      <br/>
      <p>Green are new, light green are newer and red to be removed</p>
      <div class="left">
         <h2>Merged Lists</h2>
         {local:merge($list1/item, $list2/item)}
      </div>
    </body>
</html>

Execute