Page 1 of 1

not getting accurate age by mysql function

Posted: Tue Mar 26, 2013 1:59 am
by infomamun
Hello there,
I store Date of Birth(DoB) in a mysql database from a html post form.
So if a user enter 1957-03-07 as his DoB, then it stores as 1957-03-07 00:00:00 in mysql database which field name is 'dob'.
Now I want to calculate age between current date and DoB by a php script.
So I execute following mysql query:

Code: Select all

$result = mysql_query("SELECT *,DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),dob)), '%yy %mm %dd') AS age FROM $table WHERE user_name like '%$username%'") or die(mysql_error());

while($row=mysql_fetch_assoc($result)){
echo $row['age'];
}
Suppost today/NOW() is '2013-03-26'. So the age should be 56years 0months 19days (DoB:1957-03-07).
But the fetched output from the mysql query is showing age as 56years 01months 20days. I can correct days by deducting 1 in mysql query like this: ..........DATE_FORMAT(FROM_DAYS((DATEDIFF(NOW(),dob)-1)).......... but how about months?
Why it is adding 01 month with actual age?
How to eliminate this imperfection?

Best Regards

Re: not getting accurate age by mysql function

Posted: Tue Mar 26, 2013 2:17 am
by requinix
Because you're trying to treat a date interval as if it were a date.

Imagine if MySQL started counting dates from 1900 (it doesn't). The DATEDIFF might give a value of, say, 1. You then pass it to FROM_DAYS() which creates 1900-01-02 (that is, one day after the beginning of 1900). You then format it and get "00y 01m 02d". The difference is that it starts counting with month 1 and day 1 while you expect it to count from month 0 day 0.

Rather than hack through it and subtract a month and day, I would use PHP's DateInterval class to calculate and display the age (by taking a DateTime for now and diff()ing it with a DateTime for the birthdate).

Re: not getting accurate age by mysql function

Posted: Tue Mar 26, 2013 4:14 am
by infomamun
Thanks requinix for your advice to calculate age accurately.
This is the code to calculate the age in years, months and days:

Code: Select all

$today = date_create('2013-03-26');
$birthday = date_create($row['dob']);
$diff = date_diff($today, $birthday);
      echo 'Your age is: '.date_interval_format($diff, "%y years %m months and %d days");