Sorting a HTML table on a column whose sortable value is not displayed. Any existing solutions?

Question

I know that there are many table sorters out there. For example, tablesorter, datatables, etc. But I did not find anything ready out there where I can sort the column on some internally associated value. Easy to explain example: Date and time is retrieved as a simply UNIX integer time but display is formatted using a formatting string that may not provide ascending or descending dates, if sorted on the ASCII displayed string. E.g. sort on a date displayed like 12/31/2011 will not provide temporal sorting. I know that there are options in sort sorters to sort by date specifically but I am looking for a generic solution for datatypes that go beyond standard types such as date.

Any suggestions would be welcomed. I want to code in JQuery. I am looking more for an algorithmic approach rather than how to code. For example, where to maintain the hidden value? Data-xxx attribute for example in the "td" of the column and have that same attribute set to a boolean Y or N in the "th" of that column to indicate sort type on display or data value?

If existing solutions exist, please mention them. I did not find any but then again I may have missed one.

Answer 1

I use Jquery Datatables (http://datatables.net/). You can extend the pluggin to add new "data format".

Example:

I'm from Spain and the date is represented by the format: dd/mm/yyyy hh:mm:ss.

If I use the sorter that the pluggin use to order dates, the date is ordered like a string (the pluggin suposes the data format is yyyy-mm-dd hh:mm:ss).

You have to do a function to return an integer that is the "weight" of the date, '20/10/2013' has less weight than '15/11/2013' so 20/10/2013 is lesser than 15/11/2013.

For the format of dd/mm/yyyy the function to calculate the weight is: (you can change to mm/dd/yyyy changing the indexes of frDateParts)

function dateHeight(dateStr){
        if (trim(dateStr) != '') {
                var frDate = trim(dateStr).split(' ');
                var frTime = frDate[1].split(':');
                var frDateParts = frDate[0].split('/');
                var day = frDateParts[0] * 60 * 24; /*<--- in mm/dd/yyyy is frDateParts[1]*/
                var month = frDateParts[1] * 60 * 24 * 31; /*<--- in mm/dd/yyyy is frDateParts[0]*/
                var year = frDateParts[2] * 60 * 24 * 366;
                var hour = frTime[0] * 60;
                var minutes = frTime[1];
                var x = day+month+year+hour+minutes;
        } else {
                var x = 99999999999999999; 
        }
        return x;
}

And the extension of the pluggin:

/* NOTE: the new format is called "date-euro" (why not? xD) so:
you have to do the "date-euro-asc" for ascendent order and the "date-euro-desc"
 for descendent order. the return of the function has to be 
-1 (lesser), 0 (equal) or 1 (greater)*/

jQuery.fn.dataTableExt.oSort['date-euro-asc'] = function(a, b) {
        var x = dateHeight(a);
        var y = dateHeight(b);
        var z = ((x < y) ? -1 : ((x > y) ? 1 : 0));
        return z;
};

jQuery.fn.dataTableExt.oSort['date-euro-desc'] = function(a, b) {
        var x = dateHeight(a);
        var y = dateHeight(b);
        var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));
        return z;
};

and when you define the table with the datetables plugin to that column:

 { "sType": "date-euro"},

Example (table with 5 columns (Int, string , Date,string, string):

  "aoColumns": [
         { "sType": 'numeric' },
           null,
           { "sType": "date-euro"},
           null,
           null 
     ],