Ordering by Date Time not working

JabaHPU

I can't get the DateTime column on my table to order correctly. It seems to be ordering by string values. The data for my table is coming from a query of an oracle database. The DateTime column returned from the query is of DateTime type and is in this format "2021-12-17 17:55:18.000". This table is within a Django project and works perfectly other than this one column. It displays like this "Dec. 18, 2021, 9:38 a.m." when the table is rendered in the browser. I am using the responsive javascript setup:

    $(document).ready(function () {
        var table = $('#table').DataTable({
            responsive: true
        });
        new $.fn.dataTable.FixedHeader(table);
    });

I've seen where you can set data-order to a timestamp, but since this data is coming from a query and is dynamic I'm having issues finding a solution.

kthorngren

See this blog about using the datetime sorting plugin.

Kevin

JabaHPU

Thanks for the response Kevin,

I've been trying to use moment.js and it works, but at the expense of the responsive feature, pagination, and the ability to toggle sorting from ascending to descending. I've tried to figure out how to add the moment plugin CDN into the minified CDN that was created from the Download section, but I can't resolve that yet.

This is my current setup:

// CSS CDN
<link rel="stylesheet" type="text/css"
href="https://cdn.datatables.net/v/bs5/jq-3.6.0/dt-1.11.3/date-1.1.1/r-2.2.9/datatables.min.css" />

// Moment.js CDN
<script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.11.3/sorting/datetime-moment.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
// Minified CDN from Download page
<script type="text/javascript"
src="https://cdn.datatables.net/v/bs5/jq-3.6.0/dt-1.11.3/date-1.1.1/r-2.2.9/datatables.min.js"></script>

// JS table function 
<script type="text/javascript">
    $(document).ready(function () {
        // Responsive, Sort toggle, and pagination go away when I add this line
        // But Datetime sorting works with this line though
        $.fn.dataTable.moment('YYYY-MM-DDThh:mm:ss');

        var trans_table = $('#trans_table').DataTable({
            responsive: true
        });

        new $.fn.dataTable.FixedHeader(trans_table);
    });
</script>

This is within a Python Django project. In VS Code debugger, the Datetime value being returned from the query on the Oracle database shows up like this:
datetime.datetime(2021, 1, 2, 14, 9 , 30)

and when you print this value to console or when you use a DBMS like DBeaver it shows up in YYYY-MM-DD hh:mm:ss format:
2021-01-03 14:09:30

Now when it is displayed in the table in the browser:
Jan. 3, 2021, 2:09 p.m.

I'm not sure what is changing it from the console format to the browser format.

I am using Bootstrap5 in my Django project, and in the Datatables.net Download section, I selected Bootstrap5 and JQuery. Not sure if that makes a difference.

I would like this table to have sorting on all columns, it needs to be responsive for mobile, it needs pagination because sometimes it shows 100s of records. All these features worked except for the Datetime Column. When I add the moment.js code, it either stays the same or all the features above stop working depending on how a setup the code.

JabaHPU

Not sure why that posted twice

kthorngren

Generally when the Datatable is not formatted, etc there is an error stopping Javascript. Take a look in the browser's console for errors.

I believe datetime-moment.js has a dependency of datatables.js. Move lines 6 and 7 below line 10.

$.fn.dataTable.moment('YYYY-MM-DDThh:mm:ss');

I don't believe the format string you specified matches 2021-01-03 14:09:30. I think the T needs to be replaces with a space. Refer to the moment format docs for datetime tokens.

Now when it is displayed in the table in the browser:

It doesn't look like you are using ajax to load the data. I suspect you are using a template so this format will be based on your Django template.

If this doesn't help then we will need to see a link to your page or a test case replicating the issue to help debug. Take the raw HTML table from your Django template to build a test case.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

JabaHPU

Thanks again for the help Kevin,

I finally resolved the issue. It seems that Datatables didn't want to work with the format of the date being sent to it.

I created a function that converts the Python DateTime.DateTime object being returned from the query into milliseconds and then I added this value to each dictionary entry being sent to my table.

for dt in bbts_gen_acc_trans:
   trans_date = dt["DateTime"]
   trans_date_ts = int(round(trans_date.timestamp())*1000)
   dt["JS_TimeStamp"] = trans_date_ts

I then applied this value in my Django template to the data-order property of the table row that the date was being displayed in. This allows the column to sort based on the timestamp value of JS_TimeStamp instead of the Python DateTime object representation.

<td data-order = "{{ t.JS_TimeStamp }}">
    {{ t.DateTime }}
</td>

desperado

@JabaHPU FYI, since you have the other data now you can use orthogonal data to return the value from JS_TimeStamp when your formatted date column is used for sorting. That's what I do but I actually just return both values as one with a delimiter. I split on the delimiter and return one value for sort and the other for everything else.

https://datatables.net/manual/data/orthogonal-data