Sorting dates, handling null values

Sorting dates, handling null values

RickFRickF Posts: 9Questions: 0Answers: 0
edited January 2010 in Bug reports
Null dates are currently treated as being equal to "1/1/1970", which leads to non-intuitive behavior when sorting a list which contains nulls and dates before and after that date. I suggest the following change:
[code]
"date-asc" : function (a,b)
{
var x = Date.parse(a);
var y = Date.parse(b);
if (x == y) { return 0; }
if (isNaN(x) || x < y) { return -1; }
if (isNaN(y) || x > y) { return 1; }
},
"date-desc" : function (a,b)
{
var x = Date.parse(a);
var y = Date.parse(b);
if (x == y) { return 0; }
if (isNaN(x) || x < y) { return 1; }
if (isNaN(y) || x > y) { return -1; }
}
[/code]

Replies

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin
    Hi RickF,

    Sounds like a reasonable change to make - although it isn't something that can occur 'automatically' since the 'date' type detection function checks to see if the result from Date.parse() is not "not a number". So I guess you have the 'sType' for a column set to 'date' manually? Or is there a different bug here as well :-)

    Regards,
    Allan
  • RickFRickF Posts: 9Questions: 0Answers: 0
    Indeed, I hadn't looked at the type detection code because I had set the sType. Looking at it now, I see a typo in a comment on line 674 ("Snaity" instead of "Sanity"), but that's probably not a real problem :)

    Personally, I think that ignoring blank data elements is a good way to go, but I could see the case for doing it the way it is now. If you wanted to ignore blanks, you might do something like this modification to the code around line 2152:
    [code]
    else if ( oSettings.aoColumns[i].sType != "string" )
    {
    if (aData[i] !== null && aData[i].length > 0)
    {
    if ( oSettings.aoColumns[i].sType != _fnDetectType( aData[i] ) )
    { oSettings.aoColumns[i].sType = "string"; }
    }
    }
    [/code]
    I noticed a couple of problems with the way it's coded now. Most importantly, if you have a column containing only "date" and "number" items, the auto-detection is going to return whichever type is last on the list. Second, it doesn't handle user-added data types. This snippet should ensure that all non-blank data elements in a column are the same type - if not, it sets the column to 'string'.

    I'm not sure "aData[i].length > 0" is actually the right test condition - I think I'd rather do a regex check for non-spaces; but I'm not really familiar with doing regexes in Javascript, so I'm not sure what the right syntax is nor what the performance penalty would be (if any).

    In the course of testing, I found that the "_fnDetectType" function fails if you pass it a "null", which can happen with a JSON datasource. I'm testing for null in my code above, but if you want to handle blanks differently, keep an eye on that.
  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin
    Hi RickF,

    Thanks for your detailed reply (typo now fixed in my source ;-) ).

    Regarding your point about date and number types in the same column - yes I see the problem here. It will take which ever sorting type the last row happens to have a type of. I'll have a look at that for the next release. DataTables assumes that each column is of a specific data type (it's possible to get around this assumption with a sorting plug-in though).

    Also, DataTables does provide a method for user added types to be auto detected: http://datatables.net/development/type-detection . And this is it in action: http://datatables.net/examples/plug-ins/sorting_plugin.html .

    Regarding the null being passed to _fnDetectType - null isn't valid data (indeed by definition, it's the absence of data!), therefore it can't be shown in a table, and thus DataTables doesn't cope with it. There is a discussion about this ( http://datatables.net/forums/comments.php?DiscussionID=1051 ), where one option is for DataTables to replace the null with an empty string (which is presumably how most developers would want 'no data' to be displayed) - but then DataTables is modifying the data to be displayed - which I don't really like the idea of.

    Regards,
    Allan
  • RickFRickF Posts: 9Questions: 0Answers: 0
    To clarify, the current code will auto-detect a custom datatype, but it only looks at the first row - the code for checking that each cell in a column is of the same type only fires on "date" and "numeric" typed columns.

    I don't agree that a null is invalid data by definition - consider a table where you want to display the wedding anniversary of all your employees - a null date would be perfectly valid data for any unmarried person.

    As the code stands currently, you have inconsistent behavior in handling nulls. If you have a defined sType, it adds the row, 'casting' the null by setting the td.innerHTML to null[*]. If not, it silently fails, and doesn't process any further rows in the data set. (I'm using the fnAddData method, passing it a JSON array of arrays.)

    If you decide that null really is an invalid value to pass to _fnDetectType, then it should throw an error of some kind if a null is passed in.

    [*] In Firefox, this results in an empty cell. IE8 displays the word "null".
  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin
    Hi RickF,

    Very good point about the first row only being checked. That is wrong, and I'll fix that - thanks :-)

    I'm still not sure about null data though - IE and Firefox can't agree on how to display null, as you point out, and I can't think what the best way to display null is. It's the absence of data, so should it be an empty cell with no child elements at all (including an empty text node - probably the technically most accurate options, but the browsers might not like it). Also how would filtering cope with null data, ignore it, treat it as a string, treat it as an empty string? I think all in all, I might have DataTables throw an error when it encounters null data. Replacing it with an empty string is the the other option I think, but perhaps that should be up to the developer. Perhaps also the date detection show allow for empty strings in the input data... so many options :-)

    Regards,
    Allan
  • lokiloki Posts: 3Questions: 1Answers: 0
    Hi Allan,

    First off, great work on this plugin!

    I understand that you and RickF are talking about two separate issues here. First on how to sort date columns and second on how to auto detect date columns when they contain null.

    I'm not sure on the second discussion but i do agree that nulls should always come after actual dates when sorting asc or desc so that the dates show at the top. RickF's first code snippet was exactly what i was looking for. I just wanted to add that there was a mistake in his asc code. You got to flip the x,y isnan check (the desc works as is):

    [code]
    "date-asc": function ( a, b )
    {
    var x = Date.parse( a );
    var y = Date.parse( b );

    if (x == y) { return 0; }
    if (isNaN(y) || x < y) { return -1; }
    if (isNaN(x) || x > y) { return 1; }
    },
    [/code]
  • RickFRickF Posts: 9Questions: 0Answers: 0
    loki - the ordering of the isNaN() check should just determine where the blanks go.

    [code]
    // date-asc
    if (isNaN(x) || x < y) { return 1; }
    if (isNaN(y) || x > y) { return -1; }
    // date-desc
    if (isNaN(y) || x < y) { return -1; }
    if (isNaN(x) || x > y) { return 1; }
    [/code]
    Will result in the blanks always appearing at the bottom of the list.

    [code]
    // date-asc
    if (isNaN(x) || x < y) { return 1; }
    if (isNaN(y) || x > y) { return -1; }
    // date-desc
    if (isNaN(x) || x < y) { return -1; }
    if (isNaN(y) || x > y) { return 1; }
    [/code]
    Will result in blanks being treated as lower than any date - appearing first on ascending sort and last on descending.

    Also, my original code snippet has "asc" and "desc" reversed - "asc" actually sorts with newer dates at the top of the list.

    For anyone who wants to use this change without monkeying with the source, you can include this snippet in your script to override the default date sorting:
    [code]
    jQuery.fn.dataTableExt.oSort['date-asc'] = function(a,b) {
    var x = Date.parse(a);
    var y = Date.parse(b);
    if (x == y) { return 0; }
    if (isNaN(x) || x < y) { return 1; }
    if (isNaN(y) || x > y) { return -1; }
    };
    jQuery.fn.dataTableExt.oSort['date-desc'] = function(a,b) {
    var x = Date.parse(a);
    var y = Date.parse(b);
    if (x == y) { return 0; }
    if (isNaN(y) || x < y) { return -1; }
    if (isNaN(x) || x > y) { return 1; }
    };
    [/code]
  • lokiloki Posts: 3Questions: 1Answers: 0
    Ah i was mistaken on what your were attempting to do with the code. I thought from the conversation that blanks should be at the bottom with either asc or desc as i think they should be, nothing there, nothing to sort:). But i see what you were doing now. Anyways thanks for the snippet, it pointed me in the right direction with what i wanted to do!
  • RickFRickF Posts: 9Questions: 0Answers: 0
    Honestly, I don't remember what my goal was at the time, but I thought it might be helpful to spell out the options - I can imagine that either behavior might be desirable depending on the particular situation.

    In any case, I'm glad that my code snippet was helpful - the other rambling is really just me thinking out loud, feel free to disregard it ;)
  • PaoloValladolidPaoloValladolid Posts: 35Questions: 0Answers: 0
    I found this thread while trying to solve a date sorting issue (need to sort mm/dd/yyyy, and treat blank dates as "lowest"). Just wanted to show my appreciation for the helpful discussion. I did have to change the isNaN() checks above though, because I was getting descending order for ascending sort and vice versa.

    [code]
    jQuery.fn.dataTableExt.oSort['us_date-asc'] = function(a,b) {
    var ukDatea = a.split('/');
    var ukDateb = b.split('/');
    var x = (ukDatea[2] + ukDatea[0] + ukDatea[1]) * 1;
    var y = (ukDateb[2] + ukDateb[0] + ukDateb[1]) * 1;
    if (isNaN(x) || x < y) { return -1; }
    if (isNaN(y) || x > y) { return 1; }
    return ((x < y) ? -1 : ((x > y) ? 1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['us_date-desc'] = function(a,b) {
    var ukDatea = a.split('/');
    var ukDateb = b.split('/');

    var x = (ukDatea[2] + ukDatea[0] + ukDatea[1]) * 1;
    var y = (ukDateb[2] + ukDateb[0] + ukDateb[1]) * 1;
    if (isNaN(y) || x < y) { return 1; }
    if (isNaN(x) || x > y) { return -1; }
    return ((x < y) ? 1 : ((x > y) ? -1 : 0));
    };
    [/code]
This discussion has been closed.