411 REFERENCE · Wikibooks
XQuery/Time Comparison with XQuery
Reference material by Wikibooks contributors. This retains its source identity and is not 411 news reporting.
Motivation
You have two identical lists of items with timestamps. You want to compare the items to see what items are newer.
Method
We will write a function that compares the timestamps of the items in two lists.
Sample Data Sets
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
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
center|frame
Sample Test Driver
<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
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