CODEWITHSUNDEEP
javascriptjquerymysqljqgridfree-jqgrid
low_rents
low_rents

Reputation: 4481

jqgrid: MySQL specific problems when sorting a date column

After running into this problem a couple of times in the last months (I always kept forgetting what the source of this problem was), I decided to post my question and answer here.

It is about a problem with sortable date columns in your colModel; here a simple example:

{
    name:'my_date', index:'my_date', width:100, search: false, sort: true,
    sorttype: 'date', formatter: 'date', 
    formatoptions: {srcformat: 'Y-m-d', newformat: 'd.m.Y'}
}

when trying to sort this column by clicking on its title in jqgrid, i kept getting this error:

Uncaught TypeError: u.parseDate.call(...).getTime is not a function

I was searching everywhere on the net, and could not find anything. It seemed like getting this error in combination with jqGrid was a very rare case.
I double and triple-checked everything in my jqGrid but there seemed to be no mistake on my part.


UPDATE
since I accepted Oleg's answer, here is what was causing the problem and my workaround:

After taking a look at the data in the database I saw a lot of "zero dates" in the date column. Older MySQL databases often used this instead of NULL: "0000-00-00" or "0000-00-00 00:00:00".

It turned out that this was causing the problem - jqgrid can't handle these "zero dates". So all i had to do was to manipulate my MySQL resultset:

CASE WHEN `my_date` = '0000-00-00' THEN ''
    ELSE `my_date`
END `my_date`

Upvotes: 1

Views: 213

Answers (2)

Oleg
Oleg

Reputation: 222017

Thank you for the bug report! The reason of the problem is the following. The internal method jgrid.parseDate returns the string " " (see the lines) in case of the date 0000-00-00:

if (ts.m === 0 && ts.y === 0 && ts.d === 0) {
    return " ";
}

On the other side the code 

findSortKey = function ($cell) {
    return jgrid.parseDate.call(context, dfmt, $cell).getTime();
};

just calls .getTime() to the result of $.jgrid.parseDate. I committed now the fix which modifies the above code of findSortKey to the following

findSortKey = function ($cell) {
    var datetime = jgrid.parseDate.call(context, dfmt, $cell);
    // datetime could be the string " "
    return datetime instanceof Date ? datetime.getTime() : 0;
};

It should fix the problem which you reported. Please try the new sources of free jqGrid.

Upvotes: 1

low_rents
low_rents

Reputation: 4481

After taking a look at the data in the database I saw a lot of "zero dates" in the date column. Older MySQL databases often used this instead of NULL: "0000-00-00" or "0000-00-00 00:00:00".

It turned out that this was causing the problem - jqgrid can't handle these "zero dates". So all i had to do was to manipulate my MySQL resultset: 

CASE WHEN `my_date` = '0000-00-00' THEN ''
    ELSE `my_date`
END `my_date`

Upvotes: 1

Related Questions