jquery datatables: Add calculated time duration to column

Question

I display the date and time in one of the columns in the datatable and want to use to calculate the time duration. How can I achieve this on datatables

I want to calculate the active from date time of each row to current date time and add it to the query type column.

This is what I have so far:

$('#datatable').DataTable( {
        //"ajax": "data/arrays.txt"
        "processing": true,
        "serverSide": false,
        "searching": false,
        'serverMethod': 'post',
        'ajax': {
            'url': "php/testing.php",
            'data': function(data){
                data.tasksts = "Open"
        }
        },
        "columns": [
            { data: 'taskid', "visible": false },          
            { data: 'activefrom' },
            { data: 'querytype' }
        ]
    });

Rory McCrossan · Answer 1 · 2021-03-16T09:49:15.857

To achieve this in JS you can use the render property of the Datatable to calculate a value and display it per row of the table.

The more tricky part will be converting the DD-MM-YYYY format to MM-DD-YYYY so that JS can convert it to a Date object (ref), and then formatting the result to an HH:MM:SS value (ref).

Try this:

$('#datatable').DataTable({
  "processing": true,
  "serverSide": false,
  "searching": false,
  // AJAX removed for this example
  "columns": [{
    data: 'taskid',
    visible: false
  }, {
    data: 'activefrom'
  }, {
    data: null,
    render: (data, type, row) => {
      let from = new Date(dmyToDate(data['activefrom']));
      let diffSecs = Math.abs((new Date().getTime() - from.getTime()) / 1000);
      return secondsToDhms(diffSecs);
    }
  }]
});

// Amended from: https://stackoverflow.com/a/64454486/519413
function dmyToDate(dateStr) {
  let arr = dateStr.split(' ');

  let dateArr = arr[0].split('-');
  let dd = (dateArr[0] || '').padStart(2, '0');
  let mm = (dateArr[1] || '').padStart(2, '0');
  let yyyy = (dateArr[2] || '').padStart(2, '0');

  let timeArr = arr[1].split(':');
  let hh = (timeArr[0] || '').padStart(2, '0');
  let mi = (timeArr[1] || '').padStart(2, '0');
  let secs = (timeArr[2] || '').padStart(2, '0');
  mm = (parseInt(mm) - 1).toString(); // January is 0    
  return new Date(yyyy, mm, dd, hh, mi, secs);
}

// https://stackoverflow.com/a/52387803/519413
function secondsToDhms(seconds) {
  seconds = Number(seconds);
  let d = Math.floor(seconds / (3600 * 24));
  let h = Math.floor(seconds % (3600 * 24) / 3600);
  let m = Math.floor(seconds % 3600 / 60);
  let s = Math.floor(seconds % 60);

  let dDisplay = d > 0 ? d + (d == 1 ? " day, " : " days, ") : "";
  let hDisplay = h > 0 ? h + (h == 1 ? " hour, " : " hours, ") : "";
  let mDisplay = m > 0 ? m + (m == 1 ? " minute, " : " minutes, ") : "";
  let sDisplay = s > 0 ? s + (s == 1 ? " second" : " seconds") : "";
  return dDisplay + hDisplay + mDisplay + sDisplay;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css" />
<table id="datatable">
  <thead>
    <tr>
      <th>TaskID</th>
      <th>Active from</th>
      <th>Query type</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Foo</td>
      <td>23-03-2021 10:54</td>
      <td>Foo</td>
    </tr>
    <tr>
      <td>Bar</td>
      <td>25-03-2021 15:25</td>
      <td>Bar</td>
    </tr>
  </tbody>
</table>

jquery datatables: Add calculated time duration to column

1 Answers1