Bug in sorting

Bug in sorting

Shinji62Shinji62 Posts: 5Questions: 0Answers: 0
edited August 2009 in General
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]

Replies

  • Shinji62Shinji62 Posts: 5Questions: 0Answers: 0
    here data [code]
    {"iTotalRecords": 55, "iTotalDisplayRecords": 55, "aaData": [ ['Attach
  • allanallan Posts: 63,599Questions: 1Answers: 10,486 Site admin
    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
  • Shinji62Shinji62 Posts: 5Questions: 0Answers: 0
    edited August 2009
    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.
  • allanallan Posts: 63,599Questions: 1Answers: 10,486 Site admin
    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
  • Shinji62Shinji62 Posts: 5Questions: 0Answers: 0
    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
    ------------------------
  • Shinji62Shinji62 Posts: 5Questions: 0Answers: 0
    I am sorry the mistake was in the ajax source file, i think sort function don't make something when you use ajaxsource file
  • allanallan Posts: 63,599Questions: 1Answers: 10,486 Site admin
    Hi Shinji62,

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

    Regards,
    Allan
This discussion has been closed.