Time Difference problem in PHP and MySQL and its solution

Time Difference problem in PHP and MySQL and its solution

Author: Abinash Grahacharya

In many projects we need to generate the time difference between the current date and the date the record was created. For example, suppose you want to get the date difference of number of comments related to present date and time. What possibilities come to your mind ? First I thought of getting the date time from the field of the table and call a function which will compare it with the present date and time and return the date difference string to show. But the results I got were not correct. Why ? Because the value in the database was in yyyy-mm-dd hr:min:sec format. So, with PHP function we have to generate the current time in the same format to calculate the difference. In php to get present time in yyyy-mm-dd hr:min:sec format To get date in the format we have to use the date function like date('Y-m-d H:i:s');   NOTE : Y is for year in 4 digit format, m for month, d for days, H for hours,i for minutes and s for seconds- For different time stamp the H value will give different results so you can not get the correct H value always. So to get time difference in php is difficult and sometime buggy.   To get the difference it is better to get it through a MySQL query and call the function to return the difference string   Lets the rules be 1 - if there is no minutes value and no hr value means if hour and minutes is 0 show second else dont show seconds 2 - if days more then 0 days do not show the hours and minutes or seconds 3 - if months be more then 0 show months and days 4 - if years more then 0 show years, months and days     Main M?YSQL query part Suppose your Field name is `CreatedDate` and you want to generate the difference on this field which is in datetime format ( yyyy-mm-dd hr:min:sec) and suppose the table name is Comments   Your Query will be :   $result = mysql_query("SELECT *,TIMEDIFF(NOW(),`CreatedDate`) as dataDif,TIMESTAMPDIFF(YEAR,`CreatedDate`,NOW()) as years ,(TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) - (TIMESTAMPDIFF(YEAR,`CreatedDate`,NOW()) * 12)) as months,(TIMESTAMPDIFF(DAY,`CreatedDate`,NOW()) - (TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) * 30)) as days FROM `Comments` WHERE -------------  your conditions ------------- ");   you can see there is select * to select all fields [ Do it if you need all fields of the table else the required fields with comma separated form ] and after that 4 cluses   1 - TIMEDIFF(NOW(),`CreatedDate`) as dataDif 2 - TIMESTAMPDIFF(YEAR,`CreatedDate`,NOW()) as years 3 - (TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) - (TIMESTAMPDIFF(YEAR,`CreatedDate`,NOW()) * 12)) as months 4 - (TIMESTAMPDIFF(DAY,`CreatedDate`,NOW()) - (TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) * 30)) as days   By these 4 different cluses i  am generating 4 different columns called dataDif,years,months,days

Using  the while loop you can get these values one by one  :   //php part   /*******************************************************************************/ while($rst_results = mysql_fetch_array($results)) {      //showinf the difference string by passing 4 values      echo dateTimeDiff($rst_results['dataDif'],$rst_results['years '],$rst_results['months'],$rst_results['days']); } /*******************************************************************************/     You can see the php part we are calling the function dateTimeDiff with 4 parameters that we are getting from the Query : i will write the function below before that i want to tell you the format and about the 4 cluses   1 - TIMEDIFF(NOW(),`CreatedDate`) as dataDif   TIMEDIFF is a mysql function we are passing NOW() [ a mysql function generate present time in yyyy-mm-dd hr:min:sec format ] and CreatesDate which is the fields value - it will generate a new columns as dateDif in hours:min:seconds format hours can be 234 hours between 2 days   2 - TIMESTAMPDIFF(YEAR,`CreatedDate`,NOW()) as years   TIMESTAMPDIFF is also a mysql function in which we can generate diference date , month or years by passing first parameter here you can see the first parameter is YEAR so it will just return the number of YEARS difference.   3 - (TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) - (TIMESTAMPDIFF(YEAR,`CreatedDate`,NOW()) * 12)) as months   After years i need to get the remaining months in difference so here i have generate the MONTHS as TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) which will generate the months difference between 2 dates and after that subtracting the number of months of years i got   means suppose you got 2 years so if - TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW())  is giving you 28 months of 2 dats difference then you need to show only 4 months as for 2 years 12*2 = 24 months subtracted so the string will be : 2 years 4 months     4 - (TIMESTAMPDIFF(DAY,`CreatedDate`,NOW()) - (TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) * 30)) as days   In the same way we need to generate the left days so : number of days - (months*30) . (TIMESTAMPDIFF(DAY,`CreatedDate`,NOW())  will return the number of days and TIMESTAMPDIFF(MONTH,`CreatedDate`,NOW()) will return the number of months , So in this way we can get the left days       dateTimeDiff Function in PHP function dateTimeDiff($datadiff,$years,$months,$days)  {   $string = '';   if($years != 0)   {    $string = $string.$years." years ";   }   if($months != 0)   {    $string = $string.$months." months ";   }   if($days != 0)   {    $string = $string.$days." days ";   }   if($days == 0)   {    $datadiff = explode(":",$datadiff );    $hr  = $datadiff[0];    $min = $datadiff[1];    $sec = $datadiff[2];    if($hr != 0)    {$string = $string.",".$hr." hours "; }    if($min != 0)    {$string = $string.$min." minuts "; }    if($min == 0)    {$string = $string.$sec." Seconds "; }   }   $string = $string." ago ";   return $string;  }   /* According to your requirement for date difference strin you can change this function to get your formated string*/   I think this will help you to get the correct format of date difference in PHP / MYSQL :) Source