Answers

• kthorngren Posts: 20,425Questions: 26Answers: 4,794

  January 2022

  See this blog about using the datetime sorting plugin.

  Kevin
• JabaHPU Posts: 4Questions: 1Answers: 0

  January 2022 edited January 2022

  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 Posts: 4Questions: 1Answers: 0

  January 2022

  Not sure why that posted twice
• kthorngren Posts: 20,425Questions: 26Answers: 4,794

  January 2022

  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 Posts: 4Questions: 1Answers: 0

  January 2022

  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 Posts: 159Questions: 33Answers: 4

  January 2022

  @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