Sorting dates, handling null values
Sorting dates, handling null values
RickF
Posts: 9Questions: 0Answers: 0
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]
[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]
This discussion has been closed.
Replies
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
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.
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
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".
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
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]
[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]
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 ;)
[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]