date Sorting Problem with Jquery Tablesorter

Question

I am trying to sort a table which has column like 2009-12-17 23:59:59.0. I am using below to apply sort

$(document).ready(function() 
     { 
      $("#dataTable").tablesorter();  
     } 
    );

But its not working for the dates of format yyyy-mm-dd. Can any one suggest how can i apply this format for sorting?

Answer 1

The right thing to do would be to add your own parser for this custom format.

Check this to get a grasp on how that works.

jQuery Tablesorter: Add your own parser

Then take a look into the tablesorter source (search for uslongdate, shortdate and then watch how the parsers for date formats are internally done by tablesorter. Now construct your self a similar parser for your particular date format.

jquery.tablesorter.js

This should work to your liking

ts.addParser({
    id: "customDate",
    is: function(s) {
        //return false;
        //use the above line if you don't want table sorter to auto detected this parser
        //else use the below line.
        //attention: doesn't check for invalid stuff
        //2009-77-77 77:77:77.0 would also be matched
        //if that doesn't suit you alter the regex to be more restrictive
        return /\d{1,4}-\d{1,2}-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}\.\d+/.test(s);
    },
    format: function(s) {
        s = s.replace(/\-/g," ");
        s = s.replace(/:/g," ");
        s = s.replace(/\./g," ");
        s = s.split(" ");
        return $.tablesorter.formatFloat(new Date(s[0], s[1]-1, s[2], s[3], s[4], s[5]).getTime()+parseInt(s[6]));
    },
    type: "numeric"
});

Now just apply it to the column where you have this format (e.g. assuming the column your custom dates reside in are in column No. 7. (6 means column 7, because the array of the columns is zerobased)

$(function() {
    $("table").tablesorter({
        headers: {
            6: { sorter:'customDate' }
        }
    });
});

Answer 2

If you are using date/time format like mm/dd/yyyy hh:mm then use below

$.tablesorter.addParser({ 
        id: "customDate",
        is: function(s) {
            //return false;
            //use the above line if you don't want table sorter to auto detected this parser                        
            //21/04/2010 03:54 is the used date/time format 
            console(/\d{1,2}\/\d{1,2}\/\d{1,4} \d{1,2}:\d{1,2}/.test(s));
            return /\d{1,2}\/\d{1,2}\/\d{1,4} \d{1,2}:\d{1,2}/.test(s);
        },
        format: function(s) {
            s = s.replace(/\-/g," ");
            s = s.replace(/:/g," ");
            s = s.replace(/\./g," ");
            s = s.replace(/\//g," ");
            s = s.split(" ");                       
            return $.tablesorter.formatFloat(new Date(s[2], s[1]-1, s[0], s[3], s[4]).getTime());                                         
                            //new Date(year, month-1, day, hours, minutes)
        },
        type: "numeric"} );