How do I compare a datetime to current datetime and find the one closest to it?

Anders · March 14th, 2010, 04:54 AM

Just when you think you have it all sorted, a new business requirement comes along and you need to make a few changes

Basically, I've had to make a schema change by adding a new element just below the root element. The element is called 'Assignment' and can be unbounded. Also, DutyDetails can now have multiple ItemDetails. The new XML looks like this

Code:

<?xml version="1.0" encoding="UTF-8"?>
<Example xmlns:ns2="http://example.com/ns2">
	<ns2:Assignment>
		<ns2:DutyDetails ID="1">
			<ns2:ItemDetails StartDateTime="2009-02-25T19:45:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>
			<ns2:ItemDetails StartDateTime="2009-02-26T19:45:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>			
		</ns2:DutyDetails>
		<ns2:DutyDetails ID="2">
			<ns2:ItemDetails StartDateTime="2009-03-12T15:32:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>
			<ns2:ItemDetails StartDateTime="2009-03-12T15:42:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>			
		</ns2:DutyDetails>
		<ns2:DutyDetails ID="3">
			<ns2:ItemDetails StartDateTime="2009-03-12T15:40:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>
		</ns2:DutyDetails>
	</ns2:Assignment>
	<ns2:Assignment>
		<ns2:DutyDetails ID="2">
			<ns2:ItemDetails StartDateTime="2009-04-12T15:32:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>
		</ns2:DutyDetails>
		<ns2:DutyDetails ID="3">
			<ns2:ItemDetails StartDateTime="2009-03-12T15:40:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>
			<ns2:ItemDetails StartDateTime="2010-09-23T15:40:00+08:00">
				<ns2:Child>
					<ns2:One>1</ns2:One>
					<ns2:Two>2</ns2:Two>
					<ns2:Three>3</ns2:Three>
				</ns2:Child>
			</ns2:ItemDetails>			
		</ns2:DutyDetails>
	</ns2:Assignment>
</Example>

The XSLT should hopefully pick out the final element, as I set the date to later this year. Once I have found the right date I need to print everything from that particular Assignment and below.

The code that I wrote looks like this but is not working

Code:

<xsl:template match="ns2:Assignment">
	<xsl:variable name="ct" as="xs:dateTime" select="current-dateTime()"/>
<xsl:copy-of select="./ns2:DutyDetails[xs:dateTime(ns2:ItemDetails/@StartDateTime) eq min(ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)[. gt $ct])]"/>
</xsl:template>

I guess the thing I'm doing wrong lies in the first bit of the equation

Code:

ns2:DutyDetails[xs:dateTime(ns2:ItemDetails/@StartDateTime)

since ItemDetails can be unbounded it probably gets all dates for the number of ItemDetails under DutyDetails - I'm not quite sure how to call one at a time though.

Any suggestions guys to what I'm doing wrong?

Cheers
A.

Martin Honnen · March 14th, 2010, 08:41 AM

If you use

Code:

  <xsl:template match="ns2:Assignment">
    <xsl:variable name="ct" as="xs:dateTime" select="current-dateTime()"/>
    <xsl:copy-of select="ns2:DutyDetails[ns2:ItemDetails/@StartDateTime[xs:dateTime(.) eq min(//ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)[. gt $ct])]]"/>
  </xsl:template>

then the xsl:copy-of outputs those DutyDetails elements that have at least one ItemDetails child with a StartDateTime attribute for which the attribute value treated as a dateTime is the minimum of all StartDateTime values which are greater then the current dateTime.

Anders · March 14th, 2010, 02:38 PM

Martin,

I changed the code a bit, so I could get everything from Assignment and below

Code:

<xsl:template match="ns2:Assignment">
	<xsl:variable name="ct" as="xs:dateTime" select="current-dateTime()"/>
	<xsl:variable name="min" as="xs:dateTime" select="min(//ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)[. gt $ct])"/>
	<xsl:copy-of select=".[ns2:DutyDetails/ns2:ItemDetails/@StartDateTime[xs:dateTime(.) eq $min]]"/>
</xsl:template>

but it is now giving me more data than I wanted.

A few questions for you if you don't mind?
Did you add that new predicate because ItemDetails can now be unbounded?
Can you recommend a good online XPath tutorial?
Because of my recent schema changes I now seem to be further from the desired output than I was before.
I am really looking to get the following output:

Code:

<ns2:Assignment>
	<ns2:DutyDetails ID="2">
		<ns2:ItemDetails StartDateTime="2010-09-23T15:40:00+08:00">
			<ns2:Child>
				<ns2:One>1</ns2:One>
				<ns2:Two>2</ns2:Two>
				<ns2:Three>3</ns2:Three>
			</ns2:Child>
		</ns2:ItemDetails>
	</ns2:DutyDetails>
</ns2:Assignment>

Should I change my approach? By that I mean should I change my template to match on ItemDetails instead, find the date closest to 'current datetime' and then use that to somehow filter out my output?
If you see a better way of tackling my problem then don't hold back - tell me where you think I'm going wrong

(shouldn't really be working on the weekend but it get's so addictive if you can't come up with the right solution ha ha)

Thx

Martin Honnen · March 14th, 2010, 03:21 PM

Well if you do a copy-of of the 'Assigment' element then you copy it with all descendants it has in the original input. So you will have to change your approach indeed, you can do a shallow copy and then make sure only those descendants are copied that belong to the branch you are interested in:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ns2="http://example.com/ns2"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  version="2.0">
  
  <xsl:strip-space elements="*"/>
  <xsl:output method="xml" indent="yes"/>
  
  <xsl:variable name="ct" as="xs:dateTime" select="current-dateTime()"/>
  <xsl:variable name="min" as="xs:dateTime" select="min(//ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)[. gt $ct])"/>
  <xsl:variable name="itemDetail" as="element()" select="//ns2:ItemDetails[xs:dateTime(@StartDateTime) eq $min]"/>
  
  <xsl:template match="ns2:Assignment[descendant::ns2:ItemDetails intersect $itemDetail]
                       | ns2:DutyDetails[ns2:ItemDetails intersect $itemDetail]">
    <xsl:copy>
      <xsl:copy-of select="@*"/>
      <xsl:apply-templates/>
    </xsl:copy>
  </xsl:template>
  
  <xsl:template match="ns2:Assignment[not(descendant::ns2:ItemDetails intersect $itemDetail)]
                       | ns2:DutyDetails[not(ns2:ItemDetails intersect $itemDetail)]"/>
                       
  <xsl:template match="ns2:ItemDetails[. is $itemDetail]">
    <xsl:copy-of select="."/>
  </xsl:template>
  
  <xsl:template match="ns2:ItemDetails[not(. is $itemDetail)]"/>  

</xsl:stylesheet>

Note that that stylesheet, when applied to the latest input you posted, outputs

Code:

<ns2:Assignment xmlns:ns2="http://example.com/ns2">
   <ns2:DutyDetails ID="3">
      <ns2:ItemDetails StartDateTime="2010-09-23T15:40:00+08:00">
         <ns2:Child>
            <ns2:One>1</ns2:One>
            <ns2:Two>2</ns2:Two>
            <ns2:Three>3</ns2:Three>
         </ns2:Child>
      </ns2:ItemDetails>
   </ns2:DutyDetails>
</ns2:Assignment>

which has DutyDetails ID="3" and not ID="2" as you said you are looking for. If you really want the DutyDetails with ID="2" then I currently don't understand what you want to achieve, you would need to elaborate why you want that element in the output.

Anders · March 14th, 2010, 05:24 PM

WOW Martin, that certainly didn't take you long to come up with. When I look at your solution/suggestion I realise I have a lot to learn (intersect???)

With regards to the output and the ID = 3 .... well, you're absolutely right .. a mistake on my part.

I'm now going to study your stylesheet and see what I can learn from it. Once again, thank you for taking your time to help me out (and teach me).

Cheers
Anders

Anders · March 15th, 2010, 09:40 AM

Hi Martin

Your code was brilliant mate - just the example I needed. I modified certain bits so it would work with my complete XSD schema, for instance, DutyDetails has a 'sibling' that I need to output too and similar with a 'GenericError' complex type that sometimes needs to be included at the end of the output.

Also,the approach you took to solve the problem has 'broadened my horizon' if you will - I wasn't aware that you could actually do the 'min()' bit outside a template.

I'm glad your example used commands like 'intersect' and 'as="element()" because I now know how to use them

Thanks for your help ... dont' be surprised if I have some follow-up questions

Anders

mhkay · March 15th, 2010, 10:03 AM

>I'm glad your example used commands like 'intersect' and 'as="element()" because I now know how to use them

... Thanks for your help ... dont' be surprised if I have some follow-up questions

I wonder if your approach to learning a new language is as effective as it might be? Suggesting that you know how to use a construct because you have been shown an example; and suggesting that when you need more information then your first port of call will be to ask questions on a forum... Perhaps you need a good textbook, and need to get into the habit of using it?

Anders · March 15th, 2010, 10:28 AM

Quote:

Originally Posted by mhkay

>I'm glad your example used commands like 'intersect' and 'as="element()" because I now know how to use them

... Thanks for your help ... dont' be surprised if I have some follow-up questions

I wonder if your approach to learning a new language is as effective as it might be? Suggesting that you know how to use a construct because you have been shown an example; and suggesting that when you need more information then your first port of call will be to ask questions on a forum... Perhaps you need a good textbook, and need to get into the habit of using it?

Michael,

I do get your point and also agree with you - normally I would do so.
We have just started using a new ESB that supports XSLT 2.0 and as I was only really familiar with version 1.0 I thought it would be worth trying to take advantage of some of the new functionality that comes with it. It went well in the beginning - I was using <xsl:function> and other nice features. Unfortunately, I got stuck with this tricky bit of logic that I just couldn't figure out how to deal with and thanks to the many examples that Martin provided I managed to get it all done.
Don't get me wrong - I know Martin went beyond the normal level of support that you can expect in forums like this one but sometimes it's nice when that actually happens (I would like to think that I have provided similar support in other forums).
With regards to using a good textbook - our company has access to the 'safari books online', which I do make good use of. Having said that, I could not find the answer to my XSLT/XPath problem there, so I tried forums and got lucky.