I’m a big fan of jQuery DataTables plugin and have been using them for a little while now. I am a Moodle developer by profession and quite a few Moodle plugins use jQuery DataTables as their weapon of choice. DataTables can be easily added to any project using the documentation available on their website https://datatables.net/. I was working on a plugin today that needed the data to be displayed in the attached format. Note the date format and the default sorting that was needed to display the data.

jQuery DataTables and Date Column sorting

 

To achieve the above mentioned table format and sorting, I used PHP to generate the data and created a standard html table. Then to achieve default sorting and allow further sorting on the date column by the user, I used the DataTables’ datetime-moment plugin and MomentJS library along with jQuery DataTables plugin.

 

PHP Code to generate the date:

Get the date format PHP
PHP
1
2
3
4
5
6
<?php
 
//....
 
// timemodified is a UNIX epoch
date('F j, Y', $record->timemodified);

 

HTML code to generate the table

HTML to generate the table for datatable example
XHTML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
<table class="table" id="status-table">
    <thead>
    <tr>
        <th>Name</th>
        <th>Date of submission</th>
        <th>Province</th>
        <th>Course</th>
        <th>Status</th>
    </tr>
    </thead>
    <tbody>
        <tr>
            <td>Test user 1</td>
            <td>April 11, 2018</td>
            <td></td>
            <td>Work health and safety</td>
            <td>Submitted</td>
        </tr>
        <tr>
            <td>Test user 2</td>
            <td>March 28, 2018</td>
            <td></td>
            <td>First Aid</td>
            <td>Draft</td>
        </tr>
        <tr>
            <td>Test user 3</td>
            <td>March 28, 2018</td>
            <td></td>
            <td>Equal Employment Opportunity</td>
            <td>Submitted</td>
        </tr>
        <tr>
            <td>Test user 4</td>
            <td>March 27, 2018</td>
            <td></td>
            <td>Ergonomics</td>
            <td>Draft</td>
        </tr>
        <tr>
            <td>Test user 5</td>
            <td>March 27, 2018</td>
            <td></td>
            <td>New Starter Induction</td>
            <td>Draft</td>
        </tr>
    </tbody>
</table>

 

Javascript to create the datatables:

XHTML
1
2
3
4
5
6
7
8
9
10
11
<script type="text/javascript" src="jquery.dataTables.js"></script>
<script type="text/javascript" src="momentjs.js"></script>
<script type="text/javascript" src="//cdn.datatables.net/plug-ins/1.10.16/sorting/datetime-moment.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
        $.fn.dataTable.moment( 'HH:mm MMM D, YY' );
        $.fn.dataTable.moment( 'dddd, MMMM Do, YYYY' );
    
        $('#status-table').DataTable();
    } );
</script>

 

And now the actual reason behind writing this post:

I was able to build the above mentioned pretty quickly and everything was working as expected on my development machine. After testing with a few data combinations, I uploaded the code to the server. And now to my surprise, date sort was not working as expected and the column was not being sorted by the date. I spent hours trying to figure out what actually was going on as the same code was working on the development machine but was not working on the server with similar environments. Turns out that for some reason, the column was being treated as text instead of date and hence the unexpected behaviour. If you come across something similar, you add the following options to your DataTables call:

jQuery DataTables date sorting issue fix
JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
$(document).ready(function() {
     $.fn.dataTable.moment( 'HH:mm MMM D, YY' );
     $.fn.dataTable.moment( 'dddd, MMMM Do, YYYY' );
 
     var options = {
            columnDefs: [
                // here 1 is the column index for 2nd column
                { type: 'date', targets: 1 }
            ]
        };
    
     $('#status-table').DataTable(options);
});

 

And bam…. it worked on the production site too…

Hope this helps someone who is stuck at this or will get stuck in future.