Jqgrid search for dates is not working

Question

jq(function(){
    var token = window.location.href
    .substring(window.location.href
            .lastIndexOf('=') + 1);
    jq("#grid").jqGrid({
        url:'/test/umpire/getFixtures',
        datatype: 'json',
        mtype: 'GET',
        colNames:['Category',  'Tournament','Date', 'Ground','Team 1','Team 2','Umpire 1','Umpire2','Umpire 3','Match Refree','Match Obsrver','Scorer 1','Scorer 2'],
        colModel:[
            {name:'categoryName',index:'categoryName', width:100,editable:true, editrules:{required:true}, editoptions:{size:10,readonly: 'readonly'}},
            {name:'tournamentName',index:'tournamentName', width:200,editable:true, editrules:{required:true}, editoptions:{size:10}},
            {name:'matchFromDate',index:'matchFromDate', width:100,formatter: "date",sorttype: "date",formatoptions:{srcformat: "U/1000", newformat: "m/d/Y"},search:true, searchoptions: {sopt: ['eq','ne'],
                dataInit : function (elem) {
                   jq(elem).datepicker({dateFormat:'mm-dd-yy', changeYear: true, changeMonth: true, showButtonPanel: true, showOn: 'focus'});
                }}},
            {name:'groundName',index:'groundName', width:100,editable:true, editrules:{required:true}, editoptions:{size:10}},
            {name:'team1Name',index:'team1Name', width:150,editable:true, editrules:{required:true}, editoptions:{size:10}},
            {name:'team2Name',index:'team2Name', width:150,editable:true, editrules:{required:true}, editoptions:{size:10}},
            {name:'umpire1',index:'umpire1', width:100,formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},
            {name:'umpire2',index:'umpire2', width:100,formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},
            {name:'umpire3',index:'umpire3', width:100,formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},
            {name:'matchRefree',index:'matchRefree', width:100,formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},
            {name:'matchObserver',index:'matchObserver', width:100,formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},
            {name:'scorer1',index:'scorer1', width:100,formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},
            {name:'scorer2',index:'scorer2', width:100, formatter: function (cellvalue, options, rowObject) {
                return '<a href="/TNCA/umpire/assign?id=' + options.rowId + '&name='+cellvalue+'&token=${token}">'+cellvalue+'</a>';
            }},

        ],



        postData:{


      filters:'{"groupOp":"AND","rules":[{"field":"matchFromDate","op":"gt","data":"2007-09-06"},{"field":"matchFromDate","op":"lt","data":"2007-10-04"}]}' 

        },
        editurl :"/test/home/?token=${token}",
        rowNum:20,
        shrinkToFit: true,
        rowList:[10,20,30],
        height: 400,
        autowidth: true,
        rownumbers: true,
        pager: '#pager',
        sortname: 'matchFromDate',
        viewrecords: true,
        height:"100%",
        sortorder: "asc",
        caption:"<h2>Assign Umpire</h2>",
        emptyrecords: "Empty records",
        loadonce: true,
        loadComplete: function(response) {
    console.log(JSON.stringify(response))

        },
        jsonReader : {
            root: "rows",
            page: "page",
            total: "total",
            records: "records",
            repeatitems: false,
            cell: "cell",
            id: "tournamentID"
        }


    });

Hi above is my jqgrid code. I have problem in searching the date field(matchFromDate). I went across many answers given in this forum but still I couldn't achieve the date search.

From json I'm getting the date like this '1432683305000'

My jQgrid version is 4.8.2 and I am using Spring MVC.

Can anyone please help me in resolving this issue? Thank you very much in advance

Sanitary

that is giving you the date as how many miliseconds from a date (1/1/1970) so if you pass it into new Date(1432683305000), you can get the current date. – Snipe

Yup i can achieve that by putting u/1000 in format option. My problem is search is not working with the date. – Sanitary

By "having problems searching the date field" could you expand? I don't see any options you are passing into search? – Snipe

Matchfromdate is the column where date will be displayed. In json response i m getting the date in milliseconds which mentioned in my post and to format that i have used u/1000 as src format in format option. So now date is getting displayed correctly in matchfromdate column. now i need to search and display rows based on date range i give in search option and u can see the date range filter in postdata. The problem is i m getting empty records whenever i gave any date on it. But the search functionality working fine for other column. I have tried with toolbar search too but it's not working – Sanitary

are you converting the date back to ms on the backend? – Snipe

Nope i didn't convert date to ms. While adding i get the request in millisecond which i convert to timestamp before saving it to my db – Sanitary

I don't use Spring MVC and I don't use Guriddo jqGrid JS 4.8.2, beacuse I develop my own fork free jqGrid after changing License agreement in 4.7.1. Nevertheless the problem should be mostly the same. If you want then I can try to help you. You need only append your question with some test JSON data. You should be clear that the first request postData.filters will be sent to the server and the server should make the filtering instead of the client. If you want that the filter applied on the client side you should change the code. – Oteliaotero

@Oteliaotero link from the gist you can get the json data from the server. matchFromDate is the key which contains the date in millisecond. please help me how to achieve search using date. And I am very new to jqgrid i didnt understand the thing you said about postData.filters – Sanitary

What kind of functionality you need to implement? Your code contains filters property of postData parameter with some strange values. Is it your attempt to implement something? You code contains dataInit with Datepicker, but the grid have no searching interface where Datekicker would be visible. Do you need the pager with navigator bar which can display searching dialog? Do you need the filter toolbar or both? – Oteliaotero

Do you need to search by date? I mean that 1432683305000 have the date which hours, minutes, ... Do you want to ignore all parts (hours, minutes, ...) of input timestamp with exception of the date? – Oteliaotero

link gist gives you the code that I used for search dialog and filter tool bar. I tried implementing the search only with the DATE ignoring hours and minutes. Sorry about the strange values in postdata filter, it has nothing to do with my functionality it just shows up whenever the datepicker opens. – Sanitary

Answer 1

It seems to me that main problem in your code is the usage of formatoptions:{srcformat: "U/1000"} in your code. Such property can format the code, but it will save the original values of date in the local grid data. Later you try to use jQuery UI Datepicker and it have problem to use the date in the format. You can use @ syntax (see the documentation) but you will still have two problems. The first one: the date will be displayed in the datepicker in the Unix timestamp format which is not good. The second problem: the formatoptions:{srcformat: "U/1000"} saves the dates unchanged (with hours, minutes, seconds of the input date), but you want to ignore hours, minutes, seconds and so on from the input data.

So I suggest you to use the following properties:

formatter: "date", formatoptions: {newformat: "m/d/Y"},
jsonmap: function (obj) {
    var d = new Date(parseInt(obj.matchFromDate, 10));
    return d.getFullYear() + "-" + (d.getMonth() + 1) + "-" + d.getDate();
}

with jsonmap which cut the hour, min, ... parts of the input data and convert it to the string with the format close to ISO 8601.

The jsfiddle demo uses the above code and some other settings which could be helpful for you: column templates, onSelect callback of datepicker and some other. I uses free jqGrid (currently in the version 4.9 RC1) instead of Guriddo jqGrid JS 4.8.2, but I tried almost not use free jqGrid specific features (see wiki), so it should work with Guriddo jqGrid too.

Oteliaotero