How to properly sort standard time hh:mm:ss AM/PM

markApps

Hello, I am having issues figuring out how I can sort one of my columns that contain time like hh:mm:ss am or it will show pm. It appears the time is sorting 1pm as being lower than 9am ect.... I did see that using moment.js can help and I tried the following,

Current code:

 $.fn.dataTable.moment( 'h:mm:ss A');
 $('#myTable').DataTable( {
        dom: 'Bfrtip', //Needed to display buttons
        buttons: [
            'copy', 'csv', 'pdf'
        ]
    });
    $('.btn-secondary').css('background', '#FFF');
    $('.btn-secondary').css('color', '#111');

Still not working correctly.

Not sure if it helps but I am using Node.js with express and EJS. I am storing the data locally to MongoDB and fetching the data with an api route and using ejs to display the date in the table. The date sorts correctly just not the time. Example start_time is saved in the db as "9:48:41 am" and displays on the UI the same way.

 <td><%= results.results[0].start_date %></td>
 <td><%= results.results[0].start_time %></td>

Mo Gauvin

Hi,

I had a similar issue. Take a look at the "Computed Value" section of Orthogonal Data

One part of the solution is to use the options for columns

The other part of the solution is on line six where:
var d = new Date( data * 1000 );

Instead of multiplying by 1000 I would do:
var d = new Date( data ).getTime();

The getTime() function simply returns a numerical value based on Date and Time.

A more complete example based on your own code would be:

$.fn.dataTable.moment( 'h:mm:ss A');

$('#myTable').DataTable( {
    dom: 'Bfrtip', //Needed to display buttons
    buttons: [
       'copy', 'csv', 'pdf'
    ],
    columns: [
        {
            title: "Start Date",
            data: "start_date",
            render: function (data, type, row) {
                return moment(data).format('MMMM Do YYYY');
        },  
        {
            title: "Start Time",
            data: "start_time",
            render: function (data, type, row) {
                //What the user can physically see can be different than what is sorted on; Use moment.js library or whatever
                return moment(data).format('h:mm:ss a');
            },
            //What you filter on can be physically different; Remove if not needed
            //filter: function (data, type, row) {
            //
            //  return new Date(data).getTime();
            //},
            sort: function (data, type, row) {
                //What you sort on can be physically different than what they see
                return new Date(data).getTime();
            }
            //You can hide what you are physically sorting on from the user
            //,visible: false
        }
    ],
    // Sort by Start Date and then by Start Time
    order: [[0, 'asc'], [1, 'asc']]
});

$('.btn-secondary').css('background', '#FFF');
$('.btn-secondary').css('color', '#111');

allan

The plug-in introduced in this blog post is how I'd typically recommend you do date time sorting (unless you have the integer data available for orthogonal sorting data).

Allan

colin

Hi @markApps ,

It would be worth looking at this plugin here, this is designed for ordering on time. This uses Moment.js, a good JS date/time library.

Cheers,

Colin

markApps

I think I am almost there but for the Start Date column it states the the date is invalid. I am seeing this in the console.

moment.js:293 Deprecation warning: value provided is not in a recognized RFC2822 or ISO format. moment construction falls back to js Date(), which is not reliable across all browsers and versions. Non RFC2822/ISO date formats are discouraged and will be removed in an upcoming major release. Please refer to http://momentjs.com/guides/#/warnings/js-date/ for more info.
Arguments: 
[0] _isAMomentObject: true, _isUTC: false, _useUTC: false, _l: undefined, _i: 9:48:41 am, _f: undefined, _strict: undefined, _locale: [object Object]
Error
    at Function.createFromInputFallback (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:320:94)
    at configFromString (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:2178:11)
    at configFromInput (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:2547:9)
    at prepareConfig (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:2530:9)
    at createFromConfig (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:2497:40)
    at createLocalOrUTC (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:2584:12)
    at createLocal (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:2588:12)
    at hooks (https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.js:16:25)
    at render (http://172.16.121.121:5000/Automation/Reports/pos_reports:1553:23)
    at https://cdn.datatables.net/v/bs4/dt-1.10.16/b-1.5.1/b-flash-1.5.1/b-html5-1.5.1/b-print-1.5.1/datatables.min.js:30:384

tangerine

The warning message says this:

Please refer to http://momentjs.com/guides/#/warnings/js-date/ for more info.

allan

If you can give us a link to a page showing the issue that would be useful.