Ordering formatted dates - client + Luxon: how to debug?

bertieb

Link to test case: (pending)
Debugger code (debug.datatables.net): ejoxol
Error messages shown: none relating to DT
Description of problem: follows

Summary: Despite loading a format with DataTable.datetime(datefmt) + Luxon, a date column is still being sorted lexicographically

Background: I have inherited some questionable code. One of the things they were trying to do is sort a table's date column using an ancient version of DT (1.10.19) but that didn't work as they didn't handle the date column at all.

Context: I have updated the code to use DT 2.1.8 + responsive 3.0.3, plus Luxon and jQuery 3.7.1. Following the appropriate docs page I set DataTable.datetime() -- this is either "dd.MM.yyyy" or "MM/dd/yyyy". However in either cast the date column still sorts by the first number.

    DataTable.datetime(DateFormatLuxon); // "dd.MM.yyyy" or "MM/dd/yyyy"
    new DataTable("#eventTable", {
        responsive: true,
        autoWidth: true,
        columnDefs: [
            {
                targets: "_all",
                className: "dt-body-nowrap",
            },
        ],
    });

On a hunch I tried specifying a column type for the date column by adding a second entry to columnDefs but that broke sorting of that column- it would only 'sort' in data order or reversed.

I see no errors relating to DT in the console.

No fiddle/working example yet- as the main page demo and custom format example page do work I'm willing to bet something else is going on! I searched the forums and found some examples but none with a solution that presented itself.

Question: is there a way to debug how DT interprets the table? I am wondering if something odd is going on with eg column autodetection.

Thanks in advance!

Addendum: I could rewrite the code in question, eg to use ISO8601 dates, but the more bits touched the more cans of worms are opened- I am trying to apply the lightest touch possible at first!

Images:

Data order:

Asc:

Desc:

kthorngren

Here is a simple test case with your dates shown above. ITs working as expected.
https://live.datatables.net/kecekexe/1/edit

The test case has console output that shows the type assigned to the column. You could do the same to see what is assigned, probably string if its not working. This might not be useful for troubleshooting.

I'm not sure how to debug type detection. Make sure all the cells in that column have the same format. Is the raw data exactly like 30.09.2024 or are there other characters in the cell, for example the dates wrapped in a span would cause type detection to not match the date format.

Can you update the test case to show the issue?

Kevin

kthorngren

I took a look and it looks like the function _fnColumnTypes() in dataTables.js is where type detection takes place. You could step through the function to debug.

Place a breakpoint on this line in the function:

detectedType = _typeResult(typeDetect, allOf( cache[k], settings ));

When the date column is being processed use the step into the next function call and it should take to to the DataTable.datetime function, specifically this:

            DataTable.type(typeName, {
                detect: function (d) {
                    var dt = __mldObj(d, format, locale);
                    return d === '' || dt ? typeName : false;
                },

Here you can see the cell data and if the data can be converted to the date using the format specified.

Kevin

bertieb

Here is a simple test case with your dates shown above. ITs working as expected.
https://live.datatables.net/kecekexe/1/edit

Thanks for taking the time to look into this and the reply Kevin! I agree the problem is idiosyncratic to my situation.

Is the raw data exactly like 30.09.2024 or are there other characters in the cell, for example the dates wrapped in a span would cause type detection to not match the date format.

Excellent question. I did wonder about that also. The data was in an <a> tag in the table cell -- which I figured was okay? but I will double check. Here is the data as rendered on the page via inspection; I will check what is returned by the AJAX call.

Place a breakpoint on this line in the function: (snip)

Thanks for finding the relevant bit! I'll give that a try.

Thinking it through- if there is extraneous characters/tags in the data, it would make sense that forcing the column type to date as I tried would then give strange results.

kthorngren

In this case use orthogonal data to modify the value of the sort operation to extract just the date by using columns.render as a function.

Kevin

bertieb

Right you are Kevin!

I tested by temporarily stripping out the <a>...</a> tags from the data, which then sorted correctly:

I decided to modify the data returned using the HTML5 approach by adding a data-order attribute to cell.

I further suspect I could do this in ISO8601 format and do away with Luxon + setting datetime entirely (edit: yup)- but I wanted to loop back here and confirm you were correct before I go any further

Thanks again!

kthorngren

The HTML5 data attributes only work for HTML sourced tables. If you are loading the row data via ajax the HTML5 data attributes won't work and aren't supported. However if the data is returned and you are populating an HTML table then initializing Datatables using the HTML table then the HTML5 attributes will work.

I further suspect I could do this in ISO8601 format and do away with Luxon + setting datetime entirely

Yes. See the types docs for more info.

Kevin

bertieb

If you are loading the row data via ajax the HTML5 data attributes won't work

Yup, fair point!

I wish the existing code I've inherited was doing something so organised and sensible- it actually fetches and populates an HTML table body over ajax:

    // code elided to protect the 'innocent'     
    success: function (response) {
        $('#eventTable tbody').html(response);
    }

as the saying goes- dance wi' wha brung ye...