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

Anders · March 12th, 2010, 12:03 PM

Hi guys
I really need your help with the following problem that I've been trying to solve for days.

I'm trying to go through a list of datetime values and find the one that comes right after 'current datetime' - once I have found that datetime I want to pick its parent element and everything below.

Let me give you an example of the XML I'm using:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<Example>
	<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:DutyDetails>
	<ns2:DutyDetails ID="2">
		<ns2:ItemDetails StartDateTime="2009-03-12T15:32:00+10: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>
</Example>

Let's say the current date and time is "2009-03-12T15:25:00+08:00" - that means I would like to find the StartDateTime attribute of the second ItemDetails as the datetime that comes closest to the current date and time (not interested in dates from the past). Once I have found that out I want to grab everything from DutyDetails number 2 ... like this:

Code:

	<ns2:DutyDetails ID="2">
		<ns2:ItemDetails StartDateTime="2009-03-12T15:32:00+10:00">
			<ns2:Child>
				<ns2:One>1</ns2:One>
				<ns2:Two>2</ns2:Two>
				<ns2:Three>3</ns2:Three>
			</ns2:Child>
		</ns2:ItemDetails>
	</ns2:DutyDetails>

I have tried many different options but always seem to come up short due to the limitations of XSLT. I really hope you can help by giving me an idea to how I can solve my problem.

Cheers
Anders

mhkay · March 12th, 2010, 12:21 PM

In schema-aware XSLT 2.0

min(//@StartDateTime[. gt current-dateTime()])

If not schema-aware, you'll need to cast the attribute to xs:dateTime.

If not using XSLT 2.0, it's more difficult (especially taking timezones into account).

Martin Honnen · March 12th, 2010, 12:38 PM

Do you use XSLT 2.0?
Then you can work with xs:dateTime values and use those that are greater than current-dateTime() (or any time you pass in) and take the minimum e.g.

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:ns2="http://example.com/ns2"
  version="2.0">
  
  <xsl:template match="/">
    <xsl:variable name="ct" as="xs:dateTime"
      select="xs:dateTime('2009-03-12T15:25:00+08:00')"/>
    <xsl:copy-of select="/Example/ns2:DutyDetails[xs:dateTime(ns2:ItemDetails/@StartDateTime) eq min(/Example/ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)[. gt $ct])]"/>  
  </xsl:template>

</xsl:stylesheet>

I had to make up a namespace for prefix ns2.
And while your post said you want the DutyDetails with ID="2" that code above, when run against the sample you posted, outputs the one with ID="3", as the time zone of the StartDateTime inside the DutyDetails with ID="2" causes that dateTime to be less than 2009-03-12T15:25:00+08:00.

Anders · March 12th, 2010, 12:45 PM

Yes, I'm using 2.0 so I will try your suggestions.

Thank you very much
A.

Anders · March 13th, 2010, 11:20 AM

Quote:

Originally Posted by Martin Honnen

Do you use XSLT 2.0?
Then you can work with xs:dateTime values and use those that are greater than current-dateTime() (or any time you pass in) and take the minimum e.g.

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:ns2="http://example.com/ns2"
  version="2.0">
  
  <xsl:template match="/">
    <xsl:variable name="ct" as="xs:dateTime"
      select="xs:dateTime('2009-03-12T15:25:00+08:00')"/>
    <xsl:copy-of select="/Example/ns2:DutyDetails[xs:dateTime(ns2:ItemDetails/@StartDateTime) eq min(/Example/ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)[. gt $ct])]"/>  
  </xsl:template>

</xsl:stylesheet>

I had to make up a namespace for prefix ns2.
And while your post said you want the DutyDetails with ID="2" that code above, when run against the sample you posted, outputs the one with ID="3", as the time zone of the StartDateTime inside the DutyDetails with ID="2" causes that dateTime to be less than 2009-03-12T15:25:00+08:00.

Martin,

I'm trying your code above but get a message saying 'The XSL transformation did not create any output' ..... what am I doing wrong

Martin Honnen · March 13th, 2010, 12:17 PM

The sample you posted is not even namespace well-formed. I run my XSLT sample against the following input:

Code:

<Example xmlns:ns2="http://example.com/ns2">
    <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:DutyDetails>
    <ns2:DutyDetails ID="2">
        <ns2:ItemDetails StartDateTime="2009-03-12T15:32:00+10: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>
</Example>

As I said, I had to make up a namespace for the prefix 'ns2' as your sample lacked one. I guess in reality your XML declares the namespace but I have no idea which one it has; you will need to adapt the XSLT to bind the prefix 'ns2' to whatever namespace is used in your XML input sample.

If you still have problems then post the XML input you run the stylesheet against.

Anders · March 13th, 2010, 01:13 PM

Quote:

Originally Posted by Martin Honnen

As I said, I had to make up a namespace for the prefix 'ns2' as your sample lacked one. I guess in reality your XML declares the namespace but I have no idea which one it has; you will need to adapt the XSLT to bind the prefix 'ns2' to whatever namespace is used in your XML input sample.

If you still have problems then post the XML input you run the stylesheet against.

I'm sorry if you didn't see that I edited my message but I managed to figure out my problem

I'm trying to understand how the 'min()' is read - what's evaluated first and so on. I'm kinda new to XSLT and was wondering if the code in angle brackets is evaluated first:

Code:

[. gt $ct]

or if it is this bit?

Code:

/Example/ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.)

I guess I'm a bit confused to the role that angle brackets play in XSLT. I've been trying to find the answer online but I guess I'm looking the wrong places

Thanks for the help.

Martin Honnen · March 13th, 2010, 02:01 PM

XSLT uses XPath and all you are asking about in your latest post is about some XPath expressions so searching for an XPath tutorial could help.
An expression in square brackets is called a predicate, it serves as a filter to be applied to what's on its left side.
/Example/ns2:DutyDetails/ns2:ItemDetails/@StartDateTime evalutes to a sequence of attribute nodes, /Example/ns2:DutyDetails/ns2:ItemDetails/@StartDateTime/xs:dateTime(.) evalutes to a sequence of dateTime values and then the predicate [. gt $ct] is applied to each dateTime value to filter only those out that are greater than the value of the variable 'ct'. Then the minimum of those dateTime values is computed.

For the performance of the complete stylesheet it might be better to move the computation of the minimum dateTime outside of the outer predicate and store it in a variable first.

Anders · March 13th, 2010, 02:10 PM

Quote:

Originally Posted by Martin Honnen

XSLT uses XPath and all you are asking about in your latest post is about some XPath expressions so searching for an XPath tutorial could help.

Ha Ha

Good one ..... I'll try that

Your explanations and examples have been superb - thanks for helping me out!

Anders · March 13th, 2010, 02:16 PM

Quote:

Originally Posted by mhkay

In schema-aware XSLT 2.0

min(//@StartDateTime[. gt current-dateTime()])

If not schema-aware, you'll need to cast the attribute to xs:dateTime.

If not using XSLT 2.0, it's more difficult (especially taking timezones into account).

Michael,

I have realised that I need to look into what schema-aware means - however, thanks for replying.

Cheers
Anders