sort date fields, using moment?

cris19n

I am using server side to fetch my data.
I'm rendering my date data to show it in the desired am / pm format.

but when sorted by this field it doesn't sort as it should, sort out of order.

add this line of code in this format which is the same as I am rendering, but my ordering still does not work correctly

$.fn.dataTable.moment('YYYY-MM-DD hh:mm A', 'es');

this is an example of that field and my data table setup, using server side. I want to sort by data 10

let tablep1=$('#piku-produccion').DataTable({
    dom: 'Blf<"#searchCodPpro">rtip',  //<"#SearchProduccion">
    "language": {
        "sProcessing":     "<img src='vistas/img/plantilla/200.gif' />",
        "sLengthMenu":     "MOSTRAR _MENU_ REGISTROS",
        "sZeroRecords":    "NO SE ENCONTRARON RESULTADOS",
        "sEmptyTable":     "Ningún dato disponible en esta tabla",
        "sInfo":           "MOSTRANDO REGISTROS DEL _START_ AL _END_ <br> DE UN TOTAL DE _TOTAL_",
        "sInfoEmpty":      "MOSTRANDO REGISTROS DEL 0 AL 0 DE UN TOTAL DE 0 <br>",
        "sInfoFiltered":   "(FILTRADO DE UN TOTAL DE _MAX_ REGISTROS)",
        "sInfoPostFix":    "",
        "sSearch":         "<i class='fa fa-search text-info'></i>",
        "sUrl":            "",
        "sInfoThousands":  ",",
        "sLoadingRecords": "CARGANDO...",
        "oPaginate": {
        "sFirst":    "PRIMERO",
        "sLast":     "ULTIMO",
        "sNext":     "SIGUIENTE",
        "sPrevious": "ANTERIOR"
        },
        "oAria": {
            "sSortAscending":  ": Activar para ordenar la columna de manera ascendente",
            "sSortDescending": ": Activar para ordenar la columna de manera descendente"
        },
        "buttons": {
            "copyTitle": 'agregado al porta papeles',
            "copyKeys": 'Seguir adelante <i>ctrl</i> o <i>\u2318</i> + <i>C</i> para copiar los datos de la tabla a su portapapeles. <br><br>Para cancelar, haga clic en este mensaje o presione Esc.',
            "copySuccess": {
                _: '%d líneas copiadas',
                1: '1 línea copiada copiada'
            }
        }

    },
    "processing": true,
    "serverSide": true,
    "searchDelay":2000,
    "sAjaxSource":'serverside/show.tabla.php',
    search: {search: respuesta["lote_piku"]},
    "ordering": true,
    "order": [[10, "desc"]],
    "columns": [
        { data: 1 },
        { data: 2 },
        { data: null,
            "render": function ( data, type, row ) {
                //let dt1=row[3];
                 let dt1 =`
                            <span class="badge bg-yellow pull-right" data-toggle="tooltip" data-placement="right" data-html="true" title="Esta campo <br> no tiene lote reproceso">
                            LOTE RE-PROCESO ?
                            </span>
                        `
                ;
                //console.log(row[3]);
                if (row[3]!==null) {
                   dt1=row[3];
                }else{

                }

                return dt1;

            }
        },
        { data: 4 },
        { data: 5 },
        { data: 6 },
        { data: 7 },
        { data: 8 },
        { data: 9 },
        {//this is the field that I am formatting with moment and the field that I want to sort
            "render": function ( data, type, row ) {
                //2021-08-09 11:27 AM or 2021-08-09 02:27 PM
                let dt1 = moment(row[10], ["YYYY-MM-DD HH:mm:ss"]).format("YYYY-MM-DD hh:mm A");

                return dt1;

            }
        }
    ],
    "lengthMenu": [[500, 250, -1], [500, 250, "TODO"]],
    /*activo la el scroll dentro de la tabla para poder depararme y obtener vista de todas las columnas-filas */
    "scrollY": 300,
    "drawCallback": function(row, data, index) {
        $('[data-toggle="tooltip"]').tooltip();
    },
    "initComplete": function(){
        //para evitar que el servidor y el renderizado colapse, haciendo llamadas ajax
        $("#piku-produccion_filter input").unbind().bind("input", function(e) { // Bind our desired behavior
            // If the length is 6 or more characters,search
            if(this.value.length == 6) {
                // Call the API search function
                tablep1.search(this.value).draw();
            }
        });
    }
});

note: for some unknown reason it correctly sorts the first data load in the table, but when I apply the order by clicking the field, everything gets messed up.

kthorngren

When using server side processing the sorting, searching and paging is controlled by the server script. Using $.fn.dataTable.moment('YYYY-MM-DD hh:mm A', 'es'); will only work with client side processing. Are you using a Datatables supplied server side processing script? Do you need server side processing?

Kevin

cris19n

if i am using server side processing.

file. serverside/show.tabla.php

   <?php
    require 'serverside.php';
    $table_data->get('view_tabla', 'cod', array('cod','data1','data2','data3','data4','data5','data6','data7','data8','data9','data10'));

file serverside.php

<?php
include 'serversideConexion.php';
class TableData {
    private $_db;
    public function __construct() {
        try {
            $host       = HOST_SS;
            $database   = DATABASE_SS;
            $user       = USER_SS;
            $passwd     = PASSWORD_SS;

            $this->_db = new PDO('mysql:host='.$host.';dbname='.$database, $user, $passwd, array(
                PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
        } catch (PDOException $e) {
            error_log("Failed to connect to database: ".$e->getMessage());
        }
    }
    public function get($table, $index_column, $columns) {
        // Paging
        $sLimit = "";
        if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
            $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
        }

        // Ordering
        $sOrder = "";
        if ( isset( $_GET['iSortCol_0'] ) ) {
            $sOrder = "ORDER BY  ";
            for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
                if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
                    $sortDir = (strcasecmp($_GET['sSortDir_'.$i], 'ASC') == 0) ? 'ASC' : 'DESC';
                    $sOrder .= "`".$columns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". $sortDir .", ";
                }
            }

            $sOrder = substr_replace( $sOrder, "", -2 );
            if ( $sOrder == "ORDER BY" ) {
                $sOrder = "";
            }
        }

        /*
         * Filtering
         * NOTE this does not match the built-in DataTables filtering which does it
         * word by word on any field. It's possible to do here, but concerned about efficiency
         * on very large tables, and MySQL's regex functionality is very limited
         */
        $sWhere = "";
        if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
            $sWhere = "WHERE (";
            for ( $i=0 ; $i<count($columns) ; $i++ ) {
                if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" ) {
                    $sWhere .= "`".$columns[$i]."` LIKE :search OR ";
                }
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ')';
        }

        // Individual column filtering
        for ( $i=0 ; $i<count($columns) ; $i++ ) {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                if ( $sWhere == "" ) {
                    $sWhere = "WHERE ";
                }
                else {
                    $sWhere .= " AND ";
                }
                $sWhere .= "`".$columns[$i]."` LIKE :search".$i." ";
            }
        }

        // SQL queries get data to display
        $sQuery = "SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $columns))."` FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
        $statement = $this->_db->prepare($sQuery);

        // Bind parameters
        if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
            $statement->bindValue(':search', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
        }
        for ( $i=0 ; $i<count($columns) ; $i++ ) {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                $statement->bindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
            }
        }

        $statement->execute();
        $rResult = $statement->fetchAll();

        $iFilteredTotal = current($this->_db->query('SELECT FOUND_ROWS()')->fetch());

        // Get total number of rows in table
        $sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
        $iTotal = current($this->_db->query($sQuery)->fetch());

        // Output
        $output = array(
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal,
            "aaData" => array()
        );

        // Return array of values
        foreach($rResult as $aRow) {
            $row = array();
            for ( $i = 0; $i < count($columns); $i++ ) {
                if ( $columns[$i] == "version" ) {
                    // Special output formatting for 'version' column
                    $row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
                }
                else if ( $columns[$i] != ' ' ) {
                    $row[] = $aRow[ $columns[$i] ];
                }
            }
            $output['aaData'][] = $row;
        }

        echo json_encode( $output );
    }
}
header('Pragma: no-cache');
header('Cache-Control: no-store, no-cache, must-revalidate');
// Create instance of TableData class
$table_data = new TableData();
?>