Calculate days with stored date colomn and datetime now

martin1223345

I am trying to calculate the diference in days between a stored date in colomn 'testpay' in format 2021-03-31 and the current date, in this case the result should be 55. I have done this in php on this way:

$day1 = strtotime($row['testpay']);
$day2 = strtotime("NOW");
$diff = round((($day1 - $day2) / 86400)); 

I am trying to do this with datatables on this way, this does not give the right result yet. See below code and testcase.

"columns": [ { data: null, title : 'days', render: function ( data, type, row ) { return ( (moment(data.testpay, 'YYYY-MM-DD HH:mm:ss')-moment())/86400); } },
http://live.datatables.net/banurege/1/edit

kthorngren

Google is your friend for questions that aren't Datatables specific. Searching for "moment difference between two dates" finds the [moment diff docs](moment difference between two dates) first. This doc shows how to calculate the difference between two dates using moment.js.

I updated your test case with your above code snippet then changed it to use the moment diff() method:
http://live.datatables.net/banurege/3/edit

Kevin

martin1223345

Thanks, i guess i am allmost here. See below code, is it possible to combine the two? and when the value is 0 or below it should display 0. I forgot to mention that..

{ data:  null, 
        title : '!!', 
        render: function ( data, type, row ) { 
          var update_script = moment(data.testpay, 'YYYY-MM-DD');
          var now = moment();
          return update_script.diff(now, 'days'); 
       
        }
      },
                {data: 'name',
                render: function ( data, type, row, meta ) {
                return '<font color='+row.cat_color+'>'+data+'</font>';
                } } , 

So combine the

return '<font color='+row.cat_color+'>'+data+'</font>';

and the

render: function ( data, type, row ) { 
          var update_script = moment(data.testpay, 'YYYY-MM-DD');
          var now = moment();
          return update_script.diff(now, 'days'); 

So that the day value will be colored as well?

martin1223345

Forget the color question from the last one. I used your answer from the previous question and that worked for this case to! Thanks !. All that is left is to know how to display a 0 when the result is <=0.

The updated code:

var table = $('#example').DataTable({
    data: data,
    columns: [
      {data: 'update_script'},
      {data: 'my_data'},
    
      { data:  null, 
        title : 'days', 
        render: function ( data, type, row ) { 
          var update_script = moment(data.update_script, 'YYYY-MM-DD HH:mm:ss');
          var now = moment();
           return '<font color='+row.cat_color+'>'+ update_script.diff(now, 'days') + '</font>';  
        }
      }
    ],

kthorngren

You can use an if statement or if you want to get fancy use a Ternary if.

Kevin

martin1223345

Thanks for the reaction. I know how to do it in php. Javascript is new for me. I will try, thanks !

martin1223345

Thanks, got it working ! This is my final code:

 { data:  null,
        title : 'days',
        render: function ( data, type, row ) {
          var testpay = moment(data.testpay, 'YYYY-MM-DD HH:mm:ss');
          var now = moment();
    if ( testpay.diff(now, 'days')<= 0)
    return '<font color='+row.cat_color+'>0</font>';
          else{ return '<font color='+row.cat_color+'>'+ testpay.diff(now, 'days') + '</font>'; 
    } }
      }

kthorngren

Looks good. Take a look at w3cschools for Javascript and HTML tutorials. The have online REPL that you can work directly with the tutorial examples.

Kevin