<Calculate age in MySQL ⁄ >

I will try to translate some Portuguese snippets to English. The first one I picked was, how to calculate age in MySQL.

MySQL (as far as I know ^_^'') doesn't have a function that allow us to obtain a person age, although it's not really that hard to "make" one, a few subtractions and ... a simple comparison is enough.

In many cases it's necessary to know a person's age, we have a few options for doing that:
- we insert directly the age, which's not that really dynamic, because as time goes by, of course the field age will be outdated, and consequently our data, and date, will turn to be obsolete;
- we insert directly the age and will change manually that information ... well, no way, we have many other interesting things to do in life :);
- we can make a script (maybe a cron job) that will update that information, but we'll just overload the system unnecessary
- .......
- we can also make that calculation using a server-side scripting language, such as PHP, ASP, etc., it's also a good solution, but if we could retrieve that information directly from MySQL, maybe it would be better
- we can save the date of birth and every time that it's necessary we can make a query in which we can ask gently to MySQL to do that work for us;

It will be in this last option that I'll focus.

Let's suppose we have the following table structure

-- Table "persons_data" DDL

CREATETABLE`persons_data`(

`id` smallint(6)UNSIGNEDNOTNULLAUTO_INCREMENT,

`name` varchar(255)NOTNULLDEFAULT'',

`date_birth` date DEFAULTNULL,

PRIMARYKEY(`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Let's fill it with some values:

INSERTINTO persons_data

(id, name, date_birth)

VALUES

(1, "Jet Li", "1963-4-26");

INSERTINTO persons_data

(id, name, date_birth)

VALUES

(2, "Jackie Chan", "1954-4-7");

INSERTINTO persons_data

(id, name, date_birth)

VALUES

(3, "Ji-hyun Jun", "1981-10-30");

INSERTINTO persons_data

(id, name, date_birth)

VALUES

(4, "Tae-hyun Cha", "1976-3-25");

INSERTINTO persons_data

(id, name, date_birth)

VALUES

(5, "Ye-jin Son", "1982-1-11");

We'll create a file named "settings.inc", this file will contain some settings that will be necessary to this demonstration, such as the database connection, table name, but I would just like to point out the most important part in this snippet (or this snippet wouldn't be called calculating age in mysql :)) ISQL_AUX_CALCULATE_AGE

Basically to get the age, we have to subtract the current year for the year of the birth date, however there's a small catch, of course our goal's to have the person's age always updated, for example, if that person birthday already occurred, that information's valid, but if not, the information's invalid, so we can't strictly rely only in subtracting years, we have to go a little deeper in the date analysis. Let's see these examples:
- let's suppose that this is our current date 2007-11-03
-> if the date of birth's 1990-02-11, 2007-1990=17, it's correct;
-> if the date of birth's 1990-11-04, 2007-1990=17, it's incorrect, because only tomorrow the person will reach his 17st anniversary.

Because of that, and as I said before, we have to go a little deeper in the date analysis, we have to analyze also the month & day.
When, in MySQL, we compare something the value returned will be 1 (true) or 0 (false), well, so we'll just use that small information to verify if the current date's inferior or not comparing to the person's birthday.

settings.inc

<?php

/**

* Establish the connection to the database

*

* @return DatabaseConnector

*/

function connectDB(){

$db = mysql_connect("my_server", "my_username", "my_pwd");

mysql_select_db("my_database",$db) or die("<b>connection_error</b>");

return$db;

}

//this is the auxiliary sql statement that will calculate the age

define("ISQL_AUX_CALCULATE_AGE","

YEAR(CURRENT_DATE()) - YEAR(`date_birth`) -

(

month(CURRENT_DATE()) -

month(`date_birth`)<=0 &&

(

day(CURRENT_DATE()) - day(`date_birth`)<=0

)

) As `PersonAge`

");

//table that contains all the persons data

define("TBL_DATA","`persons_data`");

?>

Using this short example...

<?php

require_once("settings.inc");

$const=get_defined_constants();

$iSQL="

Select

`id`, `name`, `date_birth`, {$const['ISQL_AUX_CALCULATE_AGE']}

From

{$const['TBL_DATA']}

";

$db=connectDB(); //establish the database connection

$sqlResource=mysql_query($iSQL) or die(mysql_error());

while($myrow=mysql_fetch_object($sqlResource)){

$str.="{$myrow->name} was born in {$myrow->date_birth} and has {$myrow->PersonAge} years.<br/>";

}

//free resources that are no longer needed

mysql_free_result($sqlResource);

mysql_close($db);

unset($myrow);

//send to output

echo$str;

?>

… will generate the following output

Jet Li was born in 1963-04-26 and has 45 years old.
Jackie Chan was born in 1954-04-07 and has 54 years old.
Ji-hyun Jun was born in 1981-10-30 and has 26 years old.
Tae-hyun Cha was born in 1976-03-25 and has 32 years old.
Ye-jin Son was born in 1982-01-11 and has 26 years old.

Please take a note that this information (age calculations) is based in 2008-06-13 and in this snippet the dates will not be updated ^_^''