" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
|
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 inhours: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
dateTimeDiff Function in PHPfunction 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*/
|
