Bug in sorting

Shinji62

Hi all,

I have made new sorting type for mysql datetime :

[code]
function mysqlTimeStampToDate(timestamp) {
//function parses mysql datetime string and returns javascript Date object
//input has to be in this format: 2007-06-05 15:26:02
var regex=/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?$/;
var parts=timestamp.replace(regex,"$1 $2 $3 $4 $5 $6").split(' ');
return new Date(parts[0],parts[1]-1,parts[2],parts[3],parts[4],parts[5]);
}



jQuery.fn.dataTableExt.oSort['date-mysql-asc'] = function(a,b){

var x = Date.parse( mysqlTimeStampToDate(a) );
var y = Date.parse( mysqlTimeStampToDate(b) );

if ( isNaN( x ) )
{x = Date.parse( "01/01/1970 00:00:00" ); }
if ( isNaN( y ) )
{y = Date.parse( "01/01/1970 00:00:00" ); }
return x - y;
};

jQuery.fn.dataTableExt.oSort['date-mysql-desc'] = function(a,b){
var x = Date.parse( mysqlTimeStampToDate(a) );
var y = Date.parse( mysqlTimeStampToDate(b) );
if ( isNaN( x ) )
{x = Date.parse( "01/01/1970 00:00:00" ); }
if ( isNaN( y ) )
{y = Date.parse( "01/01/1970 00:00:00" ); }
return y - x;
};
[/code]


But when i have lot of data, sorting on date don't work.

DataTable Declaration :
[code]
var $j = jQuery.noConflict();


$j(document).ready(function() {
$j('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./ajax/ajax_certif.php?type=<?=$g_type?>",
"oLanguage": {"sUrl": "./ajax/fr_FR.txt"},
"iDisplayLength":25,
"sPaginationType": "full_numbers",
"bAutoWidth": false,
"aoColumns": [ null,
null,
null,
{ "sType": "date-mysql" },
{ "bSortable": false }
]



} );





} );
[/code]

Shinji62

here data [code]
{"iTotalRecords": 55, "iTotalDisplayRecords": 55, "aaData": [ ['Attach

allan

Hi Shinji62,

Thanks for posting your code for this. I don't immediately see anything obviously wrong - so the first question is, does it work with a smaller data set (going by what you said earlier it sounds like it might be) - in which case it's a data issue not matching the required formatting.

If this isn't the case, the it's JS debugging time :-). It would be worth checking the return from your mysqlTimeStampToDate() function is always what is expected, and also that Date.parse() is giving a number (worth watching out for it returning 'null' - I fell into this trap recently, if it can't parse the string).

Regards,
Allan

Shinji62

Thx for answer, i trace with firebug and mysqlTimeStampToDate give allways the good things.

For example : 2009-08-26 11:03:37 return 1251277417000 and 2009-08-18 15:06:05 return 1250600765000

But in display sorting you have first 2009-08-18 15:06:05 and after 2009-08-26 11:03:37 ....

I check this :
[code]
var x = Date.parse( mysqlTimeStampToDate(a) );
var y = Date.parse( mysqlTimeStampToDate(b) );
[/code]
and all expected result is ok.

allan

Hi,

Hmm - there is certainly something odd going on here.

Your code is basically doing something like this - which looks slightly odd to me:

[code]
Date.parse( new Date(parts[0],parts[1]-1,parts[2],parts[3],parts[4],parts[5]) )
[/code]

Just before you do "return x - y;", could you check the values of x and y and make sure that they correspond to the expected date?

Is the resulting sorting random, or is it just inverted? If it's inverted, then you can just change "return x - y;" or "return x + y;" etc.

Allan

Shinji62

i have made some firebug logging and for me all is ok.

New date-mysql-asc(a=2009-08-18 15:41:05,b=2009-08-26 11:03:37)
myslqfunction(a):Tue Aug 18 2009 15:41:05 GMT+0200 x:1250602865000 , myslqfunction(b):Wed Aug 26 2009 11:03:37 GMT+0200 y:1251277417000
Result x- y : -674552000
------------------------
New date-mysql-asc(a=2009-08-26 11:03:37,b=2009-08-21 10:36:48)
myslqfunction(a):Wed Aug 26 2009 11:03:37 GMT+0200 x:1251277417000 , myslqfunction(b):Fri Aug 21 2009 10:36:48 GMT+0200 y:1250843808000
Result x- y : 433609000
------------------------
New date-mysql-asc(a=2009-08-18 15:41:05,b=2009-08-21 10:36:48)
myslqfunction(a):Tue Aug 18 2009 15:41:05 GMT+0200 x:1250602865000 , myslqfunction(b):Fri Aug 21 2009 10:36:48 GMT+0200 y:1250843808000
Result x- y : -240943000
------------------------
New date-mysql-asc(a=2009-08-21 10:36:17,b=2009-08-21 10:21:43)
myslqfunction(a):Fri Aug 21 2009 10:36:17 GMT+0200 x:1250843777000 , myslqfunction(b):Fri Aug 21 2009 10:21:43 GMT+0200 y:1250842903000
Result x- y : 874000
------------------------
New date-mysql-asc(a=2009-08-21 10:36:17,b=2009-08-21 10:23:14)
myslqfunction(a):Fri Aug 21 2009 10:36:17 GMT+0200 x:1250843777000 , myslqfunction(b):Fri Aug 21 2009 10:23:14 GMT+0200 y:1250842994000
Result x- y : 783000
------------------------
New date-mysql-asc(a=2009-08-21 10:21:43,b=2009-08-21 10:23:14)
myslqfunction(a):Fri Aug 21 2009 10:21:43 GMT+0200 x:1250842903000 , myslqfunction(b):Fri Aug 21 2009 10:23:14 GMT+0200 y:1250842994000
Result x- y : -91000
------------------------
New date-mysql-asc(a=2009-08-21 10:36:17,b=2009-08-21 10:23:47)
myslqfunction(a):Fri Aug 21 2009 10:36:17 GMT+0200 x:1250843777000 , myslqfunction(b):Fri Aug 21 2009 10:23:47 GMT+0200 y:1250843027000
Result x- y : 750000
------------------------
New date-mysql-asc(a=2009-08-21 10:23:14,b=2009-08-21 10:23:47)
myslqfunction(a):Fri Aug 21 2009 10:23:14 GMT+0200 x:1250842994000 , myslqfunction(b):Fri Aug 21 2009 10:23:47 GMT+0200 y:1250843027000
Result x- y : -33000
------------------------
New date-mysql-asc(a=2009-08-18 15:06:05,b=2009-08-18 15:03:59)
myslqfunction(a):Tue Aug 18 2009 15:06:05 GMT+0200 x:1250600765000 , myslqfunction(b):Tue Aug 18 2009 15:03:59 GMT+0200 y:1250600639000
Result x- y : 126000
------------------------

Shinji62

I am sorry the mistake was in the ajax source file, i think sort function don't make something when you use ajaxsource file

allan

Hi Shinji62,

Thanks for the update. So anything is working as expected now?

Regards,
Allan