How to store Birthdate and Age so that Age can be updated daily in PHP/MySQL?

Question

How should I store Birthdate's in MySQL so that I can easily update everyone's Age on a daily basis via a Cron Job?

Does it even make sense to store the Age AND the Birthdate so that when searches involving the Age are made, I don't have to calculate each Age on-the-fly and waste CPU resources?

If so, how should I 1) store the Birthdate, and 2) calculate the Age each day?

I can imagine the daily cron script first filtering out the user's whose Birthdate month is not the current month, then filtering out the user's whose Birthdate day is not the current day, and then incrementing by one the age of each user that is left.

Does this make sense? If so, how would I do that? Is there a better way to do all of this?

Answer 1

The simple answer is don't; never store a persons age. It changes for each person yearly but, as you say, you have to check that it's correct for every person daily.

Only store the date of birth, and then calculate the age when selecting from the database. It's only today - date of birth so takes almost no CPUs at all.

EDIT:

To expand upon my comment in ManseUK's answer there's also the possibility of failure. What happens if your server / database is down? Or your update fails to run at its specified time? Or someone comes along and runs it manually after the update already been run for that date? Or someone turns off your scheduler? There's no danger of this happening if you calculate Age as you select from the database.

To select where age is between 25 and 30 years and assuming a DATE column dateofbirth your query would be something like:

select *
  from users
 where dateofbirth between date_add( curdate(), interval -30 year )
                       and date_add( curdate(), interval -25 year )

Ensure users is indexed on dateofbirth.

Answer 2

No, don't store age, just calculate it in your queries. As for the birthday, I prefer to have all my date/time in unix timestamps (because I hate to deal with portability across date-format-changing locale settings)