Datatables not ordering date correctly

janpan

I am using server side processing, so as I understand, all sorting/ordering is done server side. However, when I click on the column header, it should send the server a post variable to apply the ASC or DESC sort order. This is not working and I'm trying to figure out where my problem lies.

I am using the default script that comes with datatables.

My dates in the database is stored as timestamp values such as 15-10-2015 10:20:30.

Now, the table displays fine, however the dates are not sorted correctly. Even if I output just the year values e.g. 2014 , it does not sort them ASC and DESC.

Instead, I get results like:

2014
2014
2015
2015
2014
2014
2015

:(

I declare the table as follows :

DemoTable = $('#table_demo').DataTable(
            {
                "order": [],
                "aaSorting" : [],
                "deferRender": true,
                "bJQueryUI": true,
                "bPaginate": true,
                "bStateSave": true,
                "processing": true,
                "serverSide": true,
                "sPaginationType": "full_numbers",
                "ajax":
                {
                    "url": "view_demo_remote.php",
                    "data":
                    {
                        "role": $_SESSION['role'],
                        "email": $_SESSION['email'],
                        "practiseid": $_SESSION['practiceid']
                    }
                },
                "columns":[
                    { "data": "first_number" , "bSortable": true },
                    { "data": "datecreated", "bSortable": true },
                    { "data": "submitted_by"},
                    { "data": "second_number"},
                    { "data": "picture","bSortable": false  },
                    { "data": "options","bSortable": false }
                ],
});

On the server, I have the following section for the columns:

$columns = array(
            array(
                'db' => 'id',
                'dt' => 'DT_RowId',
                'formatter' => function( $d, $row )
                {
                    // Technically a DOM id cannot start with an integer, so we prefix
                    // a string. This can also be useful if you have multiple tables
                    // to ensure that the id is unique with a different prefix
                    return $d;
                }
            ),
            array(
                'db'        => 'firstnumber',
                'dt'        => 'first_number',
                'formatter' => function($d, $row)
                {
                    $number = $d;
                    return substr($number, 0, 10);
                }),
            array(
                'db'        => 'datecreated',
                'dt'        => 'datecreated',
                'formatter' => function($d, $row)
                {
                    // DD/MM/YYYY HH:MM:SS
                    $date = date_create_from_format('d-m-Y H:i:s', $d);
                    return date_format($date, 'Y');
                }),
            array( 'db' => 'username',   'dt' => 'submitted_by' ),
            array( 'db' => 'secondnumber',   'dt' => 'second_number' ),
            array(
                'db'        => 'picture',
                'dt'        => 'picture',
                'formatter' => function($d, $row)
                {
                    return "<p style=\"padding:5px;\"><img src=\"".$d."\" alt=\"Picture\" style=\"width:auto;max-height:70px;border:1px solid #2d2d2d;-webkit-border-radius: 5px;-moz-border-radius: 5px;border-radius: 5px;  \"></img></p>";
                }),
            array( 'db'        => 'id', 'dt'        => 'options');
            );

and then I have this part after the columns section:

 // SQL server connection information
        $sql_details = array(
                'user' => DBUSER,
                'pass' => DBUSERPASS,
                'db'   => DBNAME,
                'host' => DBHOST
        );
 
        
        require( 'libraries/DataTables-1.10.7/examples/server_side/scripts/ssp.class.php' );
 
        $whereAll = " firstnumber <>''";
        echo json_encode(SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, "",$whereAll));

So the table displays fine, all column headers for the sortable columns can be clicked on and it sorts ASC or DESC perfectly. However, the date does not sort perfectly. If I turn off sorting off on the table and I manually add "ORDER BY datecreated ASC" to the where clause, it works perfectly, which tells me that there is nothing wrong with my date format, however, I want sorting ON.

Please help

allan

Odd - your setup looks okay. Can you give us a link to the page so I can take a look at what might be happening please? However, as you say, it is a server-side issue - I'm just not sure what would cause it. What order is the data returned in when sorting but the date column?

Allan

janpan

Hi allan,

Thanks for the reply. I recently found out that the format of the date is a bit of a mess in the database. (not my fault)

The date is a timestamp value, saved as a varchar in the following format 10-06-2014 10:13:53, and this is what's causing the problem.

I have to work with this and try make it work. My problem is now, that if I apply formatting to correct the date, it does change the format, but this does not seem to affect the sorting part.

This is how I try to format it server side in the columns array :

array(
                'db'        => 'datecreated',
                'dt'        => 'datecreated',
                'formatter' => function($d, $row)
                {
                    $newdateyear = substr(substr($d,0,10),6,4);
                    $newdatemonth = substr(substr($d,0,10),3,2);
                    $newdateday = substr(substr($d,0,10),0,2);
                    $newdate = $newdateday."-".$newdatemonth."-".$newdateyear;
                    
                    return $newdate;
                }),

This displays the date correctly in the datatable, however, has no effect on the sorting. I guess the "formatter" is just for display purposes and does not affect the sorting. I assume sorting is still done on the original database field value ? What can you suggest.

ThomD

https://datatables.net/reference/option/columns.render

janpan

@ThomD,

Please exaplain ?

ThomD

That describes how to have DT use one form of the data for display and one form for sorting.

janpan

@ThomD,

Thanks , I will give it a try.

ThomD

Take a look at this fiddle. It shows another (easier) way. Using moment.js, you can tell DT how the date if formatted. It in turn uses moment.js to internally covert to a format that sorts.

https://jsfiddle.net/3vLLvscr/19/

You need moment.js and the DT moment plug in

https://cdn.datatables.net/plug-ins/1.10.9/sorting/datetime-moment.js

allan

That will only work if the sorting were being done client-side - in this case server-side processing is being used, so a client-side sorting method isn't going to address the issue.

The date is a timestamp value, saved as a varchar

Ouch! That's the issue right there. It will be doing a string sort.

Really the only way to fix that is to convert a date/time type. The formatter has no effect since that is run after the data has been retrieved from the database.

Allan

ThomD

So using the render logic is the better approach in this case?

allan

I don't think that would resolve the underlying issue. That the data is coming back from the server in the wrong order is due to it be string sorted since it isn't the correct data type for the data being stored.

Allan

janpan

Thanks all for helping. I see two options.

1 - Fix the data in the database. (Little more difficult. Long term solution)
2 - On the server side, if the Order field is in fact the date, format the field to the correct format in the quesry string if possible.

I'm going to look at it tomorrow. The entire system is going for a re-do soon, so I might just try the short term solution. However, it shouldnt be too difficult to get the first solution done. I'll see tomorrow when I'm at the office.

Thanks for your help, and thanks allan for the software. I've been using it for a while now and it's really helped me a lot.

aaronw

I would take a walk through http://php.net/manual/en/ref.datetime.php and see if some of those functions can help you out. The best bet would be to work whatever function you desire on the row returned from the SELECT before it gets manipulated further... but I'm not sure how to do it in the built-in PHP server side libraries since I just write my own small wrappers

allan

format the field to the correct format in the quesry string if possible.

Yes - you could use an SQL function to convert the string to a timestamp - you might need to update the demo SSP class to accept SQL functions though.

First solution would be my recommendation if you are update the column type.

Allan

janpan

@allan

I created a new field called datecreated_new in the table with the new format DATETIME.
I then ran an update query similar to this:

UPDATE 'demo_table' SET 'datecreated_new' = STR_TO_DATE('datecreated', '%d-%m-%Y %H:%i:%s');

Then I dropped the old field and renamed the new one to the old ones name.
Now it works as it should.

thanks for your help :p

allan

Cunning - I like it :-)

janpan

haha :)