Sorting XSL by date in Month-Name Day-Number Year-Number format

Question

I have a Joomla SobiPro database of events that each contain a date field named "start_date" that looks like "Oct 10 2015" and "Jan 03 2016". I need to search on a different field (distance from a given zip code in miles) and then output the results sorted by those dates in ascending order.

I'm completely new to XSL/SobiPro/Joomla so I'm struggling but after a lot of googling I have found that I need to insert an XSL sort command at the location indicated below by  in the SobiPro template file common/entries.xsl:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:output method="xml" doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd" encoding="UTF-8" />
        <xsl:include href="vcard.xsl" />
        <xsl:include href="manage.xsl" />

        <xsl:template name="entriesLoop">
                <xsl:variable name="entriesInLine">
                        <xsl:value-of select="entries_in_line" />
                </xsl:variable>

                <xsl:variable name="cellClass">
                        <xsl:value-of select="floor( 12 div $entriesInLine )" />
                </xsl:variable>

                <xsl:variable name="entriesCount">
                        <xsl:value-of select="count(entries/entry)" />
                </xsl:variable>

                <xsl:comment>entries loop - start</xsl:comment>
        <div class="entry-container">
                <xsl:for-each select="entries/entry">

<!-- **** Insert Sort Command Here **** -->

                    <xsl:if test="($entriesInLine > 1 and (position() = 1 or (position() mod $entriesInLine) = 1 )) or $entriesInLine = 1">
                        <!-- opening the "table" row -->
                        <xsl:text disable-output-escaping="yes">&lt;div class="row-fluid" &gt;</xsl:text>
                    </xsl:if>
                    <div class="span{$cellClass}">
                        <xsl:call-template name="vcard" />
                    </div>
                    <xsl:if test="($entriesInLine > 1 and ((position() mod $entriesInLine) = 0 or position() = $entriesCount)) or $entriesInLine = 1">
                        <!-- closing the "table" row -->
                        <xsl:text disable-output-escaping="yes">&lt;/div&gt;</xsl:text>
                    </xsl:if>
                </xsl:for-each>
        </div>
                <xsl:comment>entries loop - end</xsl:comment>

        </xsl:template>
</xsl:stylesheet>

I am not familiar with XSL at all but I did find that if the date format was 2015 10 09 instead of Oct 09 2015 then I could replace the commented line above with this line:

<xsl:sort select="fields/field_start_date" order="ascending" />

and the sort would succeed since it appears to be sorting alphabetically. I cannot change the date format, nor can I add a second field that is the same date in that sortable format.

I also found that if I left the date format as-is I could get the sort by year and then by month name if I did:

<xsl:sort select="concat(
        substring(fields/field_start_date,string-length(fields/field_start_date)-3,4),
        substring(fields/field_start_date,string-length(fields/field_start_date)-10,3),
        substring(fields/field_start_date,string-length(fields/field_start_date)-6,2)
                         )" order="ascending" />

but that wont produce the desired ascending-dates output order because Feb comes before Jan alphabetically, etc.

I know it looks weird to be counting chars back from the end of the date to find the start of each data component (year, day, and month) but I tried counting forward from both zero and 1 and could not make it work. It seems like the month name starts at character position 9 (idk why - something about start_date being a compound field with a string+timezone+other info I expect) but then the day number doesn't start at position 13 as you'd expect and I gave up trying to find it by trial and error.

Given the above though I still need to figure out how to map the month name abbreviation to the number (Jan->01, Feb->02, etc.) and I've found various examples of ways to do that, e.g. https://stackoverflow.com/a/555536/1745001 by @DimitreNovatchev, but after a day of research and trial-and-error I just can't figure out how to rearrange that or any other similar code to fit into my existing XSL file and work in my case.

Can anyone help me figure this out?

Context:

If you got to my site, http://tournamart.com/, scroll down below the map, and enter these values in the given fields:

Distance From: [Oswego, Illinois, USA]  [(Drop Down:) 100 miles]
Start Date
    From: [10/01/2015]
    To:   [03/01/2015]

and then click on the green "Start Search" button, the site will print a list of the 9 tournaments coming up between Oct 1 2015 and Mar 1 2016 within 100 miles of Oswego. I'm trying to get the output sorted by date so the earliest tournament comes first. With not modification to the code the output, best I can tell, is instead ordered by the order it was entered in the database.

Given the proposed solution below (https://stackoverflow.com/a/32904143/1745001):

<xsl:sort select="substring(fields/field_start_date, 8 , 4)" data-type="number" />
<xsl:sort select="string-length(substring-before('JanFebMarAprMayJunJulAugSepOctNovDec', substring(fields/field_start_date, 1 , 3)))" data-type="number" />
<xsl:sort select="substring(fields/field_start_date, 5 , 2)" data-type="number" />

the output is still not sorted by date:

PUMA 2016 PRESIDENTS DAY INDOOR CUP
City: Sturtevant
State: Wisconsin
Start Date: Feb 13 2016

PUMA 2016 MARTIN LUTHER KING INDOOR CUP
City: Sturtevant
State: Wisconsin
Start Date: Jan 15 2016

Winter Freeze
City: Crown Point
State: Indiana
Start Date: Dec 04 2015

2015 Eclipse Select AT&T Oktoberfest Shootout
City: Waukegan
State: Illinois
Start Date: Oct 10 2015

Racine Lighthouse Classic - 2015
City: Franksville
State: Wisconsin
Start Date: Oct 10 2015

Sockers Nike Classic Cup Fall 2015
City: Chicago
State: Illinois
Start Date: Oct 09 2015

2015 Octoberfest Classic Presented by Quaker Oats
City: Libertyville
State: Illinois
Start Date: Oct 09 2015

WSA Columbus Day Classic
City: Wheeling
State: Illinois
Start Date: Oct 09 2015

Glen Ellyn Lakers FC 2015 Fall Classic
City: Glen Ellyn
State: Illinois
Start Date: Oct 02 2015

but when I modify the code to count characters back from the end of the date string:

<xsl:sort select="substring(fields/field_start_date, string-length(fields/field_start_date)-3 , 4)" data-type="number" />
<xsl:sort select="string-length(substring-before('JanFebMarAprMayJunJulAugSepOctNovDec', substring(fields/field_start_date, string-length(fields/field_start_date)-10 , 3)))" data-type="number" />
<xsl:sort select="substring(fields/field_start_date, string-length(fields/field_start_date)-6 , 2)" data-type="number" />

it seems like it succeeds as it now generates:

Glen Ellyn Lakers FC 2015 Fall Classic
City: Glen Ellyn
State: Illinois
Start Date: Oct 02 2015

Sockers Nike Classic Cup Fall 2015
City: Chicago
State: Illinois
Start Date: Oct 09 2015

2015 Octoberfest Classic Presented by Quaker Oats
City: Libertyville
State: Illinois
Start Date: Oct 09 2015

WSA Columbus Day Classic
City: Wheeling
State: Illinois
Start Date: Oct 09 2015

2015 Eclipse Select AT&T Oktoberfest Shootout
City: Waukegan
State: Illinois
Start Date: Oct 10 2015

Racine Lighthouse Classic - 2015
City: Franksville
State: Wisconsin
Start Date: Oct 10 2015

Winter Freeze
City: Crown Point
State: Indiana
Start Date: Dec 04 2015

PUMA 2016 MARTIN LUTHER KING INDOOR CUP
City: Sturtevant
State: Wisconsin
Start Date: Jan 15 2016

PUMA 2016 PRESIDENTS DAY INDOOR CUP
City: Sturtevant
State: Wisconsin
Start Date: Feb 13 2016

So, that is immensely helpful, thank you, and the remaining questions are:

Why do I have to count char positions back from the end?
Is there a more efficient (or robust or otherwise "better") way to do it?

Is this XSLT 1.0 or 2.0? XSLT 2.0 is much better at this sort of thing, but many open-source products are stuck on XSLT 1.0. — Michael Kay, Oct 02 '15 at 08:40
I don't KNOW but the first line of the code above says `version="1.0"` so does that mean it's XSLT 1.0? — Ed Morton, Oct 02 '15 at 08:45
What matters is what the processor can do, not what the stylesheet says. See here how to find out: http://stackoverflow.com/questions/25244370/how-can-i-check-which-xslt-processor-is-being-used-in-solr/25245033#25245033 — michael.hor257k, Oct 02 '15 at 10:12
OK, I've looked at that reference and I see a line of code. Do I add that somewhere in the file I'm editing? If so where? If not what do I do with it? What should I expect to see happen after I do it? Again, I have absolutely zero experience in this domain or using XLS. — Ed Morton, Oct 02 '15 at 10:19
Use this stylesheet instead of yours: http://pastebin.com/gGChraNG — michael.hor257k, Oct 02 '15 at 10:28

score 1 · Accepted Answer · answered Oct 02 '15 at 09:21

1

I still need to figure out how to map the month name abbreviation to the number (Jan->01, Feb->02, etc.)

You can do:

string-length(substring-before('JanFebMarAprMayJunJulAugSepOctNovDec', substring($f, 1 , 3)))

This maps the abbreviations as Jan->0, Feb->3, Mar->6, etc. which is quite sufficient for a numerical sort.

Instead of trying to manufacture a single text string, I suggest you simply use three numerical sort instructions:

<xsl:sort select="substring(fields/field_start_date, 8 , 4)" data-type="number" />
<xsl:sort select="string-length(substring-before('JanFebMarAprMayJunJulAugSepOctNovDec', substring(fields/field_start_date, 1 , 3)))" data-type="number" />
<xsl:sort select="substring(fields/field_start_date, 5 , 2)" data-type="number" />

I know it looks weird to be counting chars back from the end of the date to find the start of each data component (year, day, and month) but I tried counting forward from both zero and 1 and could not make it work.

If it still doesn't work for you, post an example of an input where it fails.

answered Oct 02 '15 at 09:21

michael.hor257k

113,275
6
33
51

Ah if only I could figure out where the input was coming from and what it looks like. Somewhere on my site there's a SobiPro database and all I know is that between there and here magic ensues and output is produced. Here's what an export of the SobIPro field looks like if that's useful: `"{""string"":""Sep 05 2015"",""timezone"":{""offset"":-18000,""timezone"":""America\/Chicago""},""timestamp"":""1441429200""}"`. I'll try your solution and get back to you. Thanks. – Ed Morton Oct 02 '15 at 09:49
@EdMorton You can see what the raw XML input looks like, by applying (only) the *identity transform* template: https://en.wikipedia.org/wiki/Identity_transform#Using_XSLT --- BTW, in most cases, the output from a DB would already be sorted by the DB, which (again, in most cases) can do it much more efficiently. – michael.hor257k Oct 02 '15 at 09:56
I'm sorry, I'm sure this is very basic and obvious to everyone else but I can't stress enough how little I know about this domain so - what does it mean to "apply the identify transform template"? Do I add one of the code snippets from that reference somewhere in the file I'm working on? Do I add a different file and if so where? Do I do something else? Your solution does work if I count back from the end of the string bt so thank you very much for that. I've updated my question to show what happens when I use your code and then the modified code. – Ed Morton Oct 02 '15 at 10:17
Just use the stylesheet shown in the link *as is* (replacing **all** your code). The result will be a replica of your input XML. Then you will know exactly what you're dealing with, instead of groping blind as you're doing now. – michael.hor257k Oct 02 '15 at 10:20
When I do that my site just displays `The page isn't redirecting properly`. – Ed Morton Oct 02 '15 at 10:27
The code you provided works great when all of the results are on one page but when I use a larger date range and the results get cut across multiple pages, the results on each page are sorted correctly but between pages they aren't (e.g. I can get an Oct 2015 result on page 2 when page 1 ended with Dec 2015). It actually seems like the pages are in reverse order! – Ed Morton Oct 02 '15 at 10:28
I don't see that your XSLT does any paginating, so this is probably a "feature" of your application. – michael.hor257k Oct 02 '15 at 10:32
Yes I suppose it is. I checked with a larger radius and date range and got 6 pages of output and confirmed it - the entries on each page are in the correct order but the pages are in precisely reversed date order so page 6 contains Oct 2015 - Dec 2015, page 5 is Jan 2016-Mar 2016, etc. – Ed Morton Oct 02 '15 at 10:34
You've certainly answered my initial question and I'll be happy to select this answer as accepted but before I do - a) do you have any other suggestions on how I can figure out why I need to count chars from the end of my date string? b) do you have any ideas on how I can resolve my new reversed pagination issue (the pagination must be happening after this XSL runs since the entries ARE sorted by date)? – Ed Morton Oct 02 '15 at 10:48
(a) I can only guess there are additional characters at the beginning of the string; (b) I would look for a way to tell your application to sort the entries before your XSLT gets applied. If this is the only way to sort records, than clearly it is flawed. – michael.hor257k Oct 02 '15 at 11:07

Sorting XSL by date in Month-Name Day-Number Year-Number format

1 Answers1

Linked