How to order column by date (oldest to newest) with datatable plugin

Question

I have an issue when I try to "order by" my date column of my table with the DataTable plugin.

I have setup my date format for it to appear like this : day of the week (full letter) day of the month (number) , month (number) and year (number)all in french .

In my code I have set it like this :

setlocale(LC_ALL, 'fr_FR');

...
...
<table class="table table-hover table-responsive display" id="table_1" >
                    <thead>
                        <tr>
                            <th scope="col">ID</th>
                            <th scope="col">Titre</th>
                            <th scope="col">Prénom</th>
                            <th scope="col">Prospect créé le </th>
                        </tr>
                    </thead>
                    <tbody>
                    <?php
            foreach($result as $key1){
                ?>
                        <tr>
                            <td scope="row"><?php echo $key1['ID']; ?></th>
                            <td><?php  echo  $key1['TITLE']; ?></td>
                            <td><?php echo  $key1['NAME']; ?></td>
                            <td><?php $date_create=$key1['DATE_CREATE']; echo strftime("%A %e %B %Y à %H h %M ", strtotime($date_create)); ?>
                            </td>
                        </tr>
                    <?php
            }
            ?>
                    </tbody>
                </table>

So it appear like this in my table : https://i.ibb.co/KjH4ctk/Capture.png

but when I try to order by date with the datatable plugin, it order by the letter of the day of the week and not really by date.

here's my js file :

$(document).ready(function () {
$('#table_1').DataTable({
        "destroy": true,
        language: {
            processing: "Traitement en cours...",
            search: "Rechercher :",
            lengthMenu: "Afficher _MENU_ éléments",
            info: "Affichage de l'élement _START_ à _END_ sur _TOTAL_ éléments",
            infoEmpty: "Affichage de l'élement 0 à 0 sur 0 éléments",
            infoFiltered: "(filtré de _MAX_ éléments au total)",
            infoPostFix: "",
            loadingRecords: "Chargement en cours...",
            zeroRecords: "Aucun élément à afficher",
            emptyTable: "Aucune donnée disponible dans le tableau",
            paginate: {
                first: "Premier",
                previous: "Précédent",
                next: "Suivant",
                last: "Dernier"
            },
            aria: {
                sortAscending: ": activer pour trier la colonne par ordre croissant",
                sortDescending: ": activer pour trier la colonne par ordre décroissant"
            }
        }
    });


});

Does someone know if there's a way to make it work properly ?

Regards,

Answer 1

Your case can be considered as orthogonal-data

Datatables Orthogonal Data

Data is complex. Not only will the data displayed in your tables have rules relating to how each data point corresponds to the others in your table, but also each data point itself can take multiple forms. Consider for example currency data; for display it might be shown formatted with a currency sign and thousand separators, while sorting should happen numerically and searching on the data will accept either form.

From documentation, there are several ways to solve your problem, but if your table already exists or generated in HTML, then its better to use HTML5 data approach.

Datatables HTML5 Data Atrributes

DataTables supports data-* attributes, which can be used to hold information visible in the DOM, but not to the end user.

Now back to your current codes, lets find out which part we need to change.

Only PHP part, lets change from this:

<?php
    foreach($result as $key1){
?>
        <tr>
            <td scope="row"><?php echo $key1['ID']; ?></th>
            <td><?php  echo  $key1['TITLE']; ?></td>
            <td><?php echo  $key1['NAME']; ?></td>
            <td><?php $date_create=$key1['DATE_CREATE']; echo strftime("%A %e %B %Y à %H h %M ", strtotime($date_create)); ?>
            </td>
        </tr>
<?php
    }
?>

Into this:

<?php
    foreach($result as $key1){
        $date_create=$key1['DATE_CREATE'];
        $timestamp = strtotime($date_create);
        $local_datetime = strftime("%A %e %B %Y à %H h %M ", $timestamp);
?>
    <tr>
        <td scope="row"><?php echo $key1['ID']; ?></td>
        <td><?php echo $key1['TITLE']; ?></td>
        <td><?php echo $key1['NAME']; ?></td>
        <td data-order="<?php echo $timestamp; ?>"><?php echo $local_datetime; ?></td>
    </tr>
<?php
    }
?>

The implementation will let Datatable to order your column base on HTML data-order (we set it as timestamp) instead your human view-able end user string.

Hope helps.