not getting accurate age by mysql function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

not getting accurate age by mysql function

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: not getting accurate age by mysql function

Post 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).
infomamun
Forum Contributor
Posts: 102
Joined: Mon Dec 28, 2009 7:48 pm

Re: not getting accurate age by mysql function

Post 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");
Post Reply