How do I sort by time (format: 5:40 PM) in javascript for use with DataTables?

Question

I have a datatable I'm using which has 5 columns ( http://datatables.net/ )

The columns are

1. Date in format of: Jan 5
2. Time in format of: 10:31 AM (xx:xx XX)
3. Columns 3, 4, 5 aren't important, they're just data that I dont care about the sorting as long as 1 & 2 are correct.

I want to sort by Date FIRST (most recent), then I want to sort by Time (most recent at top).

So Jan 5, 4:58 PM should show before 4:58 AM, and obviously all the other numbers need to work as well for all other times. The format is always the same, ie: 12:34 AM, 4:15 PM, 12:00 AM, etc.

For the date, that already works perfectly. There's only 2 days of data max in the datatable, so even when it rolls over to the 1st of the month, that will still show at the top which is fine. I have looked at the documentation and I'm confused how to do the correct sorting for my Time column.

Here is my code:

oTable = $('#posts').dataTable({
    "bSort": true,
    "aaSorting": [ [0,'desc'], [1,'asc'] ],
    "aoColumns": [
                null,
                { "sType": 'time-sort' },
                null,
                null,
                null
           ]

});

This is from here: http://datatables.net/release-datatables/examples/basic_init/multi_col_sort.html

I take it now I have to build some sort of custom sorting algorithm for time using the sType property for "aoColumns" (you can see it in the example link above where he sorts case sensitively), and I have zero idea how to do this :( I'm not quite even sure if I did this right so far. It seems to sort the two columns fine, but now I need to make it so time is proper...

Here is the other part of the code that I believe I need. (once again, this is from the example). I'm 99% sure this is where I need to put in my custom time sorting code for both ascending and decending.

/* Define two custom functions (asc and desc) for time sorting */
jQuery.fn.dataTableExt.oSort['time-sort-asc']  = function(x,y) {
    return ???;
};

jQuery.fn.dataTableExt.oSort['time-sort-desc'] = function(x,y) {
    return ???
};

Answer 1

You can do this by parsing the time from your input string into a date object and then comparing the date objects:

Working Demo here: http://live.datatables.net/erefom/2/edit#preview

Source here: http://live.datatables.net/erefom/2/edit

Also see this answer: What is the best way to parse a time into a Date object from user input in Javascript?

Answer 2

Elaborating from earlier examples; following snippet will sort even if the time cell is empty.

function getTimeValue(x) {
  // if the cell is not empty then parse it, otherwise just return 0 as the value; so it will be sorted appropriately.      
  if (x != '') {
    var time = x.match(/(\d+)(?::(\d\d))?\s*(P?)/);  
    var h = parseInt(time[1]) + (time[3] ? 12 : 0);
    if(!time[3] && parseInt(time[1])==12) h = 0;
    if(time[3] && parseInt(time[1])==12) h = 12;

    return h * 60 + ( parseInt(time[2]) || 0 );
  } else {
    return 0;
  }
}

Answer 3

This is what worked for me

jQuery.extend(jQuery.fn.dataTableExt.oSort, {
    "time-sort-pre": function(a) {
        if (a == '')
            return null;

        var time = a.match(/(\d+)(:(\d\d))?\s*(p?)/i);
        if (time == null)
            return null;

        var hours = parseInt(time[1], 10);
        if (hours == 12 && !time[4]) {
            hours = 0;
        }
        else {
            hours += (hours < 12 && time[4]) ? 12 : 0;
        }

        var d = new Date();
        d.setHours(hours);
        d.setMinutes(parseInt(time[3], 10) || 0);
        d.setSeconds(0, 0);
        return d;
    },
    "time-sort-asc": function(a, b) {
        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
    },
    "time-sort-desc": function(a, b) {
        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
    }
});

using a piece of code from this answer What is the best way to parse a time into a Date object from user input in Javascript? and tested with dataTables 1.9.4, this code need to be called after the dataTables.js as a plugin, then just need to set de sType to your column {sType: "time-sort"}. Example:

$('#datatable-list').dataTable(
    aoColumns: [{sType: "time-sort"}]
);

Assuming that your table only have one column with the time values