Jquery Datatables column rendering and sorting

23
votes

I'm working with a datatable that includes a column of mysql timestamps in the format YYYY-MM-DD HH:MM:SS. My table is set to initially sort by this column. Datatables correctly autodetects the timestamps format and sorts appropriately.

I'm now trying to alter the appearance of this column to be more user friendly, but not effect the way it gets sorted. So, I'm using the columns.render functionality like this:

{
        "data":"created_at",
        "name":"date",
        "visible":true,
        "title":"Date Created",
        "render": function(data, type, full, meta){
                var date = new Date(data);
                var options = {year: "numeric", month: "long", day: "numeric"};

                return date.toLocaleDateString('en-US', options);
        }
}

As soon as I do this, sorting no longer works correctly. I was under the impression that the render function should only effect the display of the data, but that it should still be sorted according to the underlying data on that row's data object. These are the docs I am trying to use (http://datatables.net/reference/option/columns.render).

Does anyone know how I can sort based on the actual timestamp but display a more user friendly date?

2
javascriptjqueryajaxdatatablesjquery-datatables

2 Answers

36
votes

I think I got it. I just had to tell the render function to only operate on "display" types:

{
        "data":"created_at",
        "name":"date",
        "visible":true,
        "title":"Date Created",
        "render": function(data, type, full, meta){
                if(type == "display"){
                        var date = new Date(data);
                        var options = {year: "numeric", month: "long", day: "numeric"};

                        return date.toLocaleDateString('en-US', options);
                }

                return data;
        }
},
0
votes

Well if it is a server-side datatable, what you can do is edit a json just before it is passed like in the beginning of ssp.class.php in the new datatables (tell me if you are using the old one)
NOTE: in the new datatables, it is an inbuilt function called formatter that does the same but you can use custom function like this

if ($j == 6) {
  if ($data[$i][$columns[$j]['db']] == 1) {
      $data[$i][$columns[$j]['db']] = '<label class="btn-danger disabled btn">Stopped</label>';
  } else {
      $data[$i][$columns[$j]['db']] = '<label class="btn-success disabled btn">Running</label>';
  }

here i am simply editing a 0 and 1 in my db to a Label Stopped and Running
you can do something like parse_date and store a reformatted one