How to store Birthdate and Age so that Age can be

问题:

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?

回答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.

回答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)

回答3:

Does it even make sense to store the Age

No.

I don't have to calculate each Age on-the-fly and waste CPU resources?

As a matter of fact, you'd waste a zillion more "CPU resources" (of which you have too vague idea to be concerned of) with your everyday update approach.

Is there a better way to do all of this?

Store the birthdate and calculate the age at select time

what if you want to find out all the ones whose Age is greater than 25 but less than 30?

this is quite trivial query like this

WHERE birth_date BETWEEN date_sub(curdate(), INTERVAL 25 YEAR) 
                     AND date_sub(curdate(), INTERVAL 30 YEAR)

the query would using an index (if any) and thus be blazing fast, without any [unnecessary] denormalizations

回答4:

Im going to go against the majority all of the answers here.

I would store both ...

• updating the age is quick and simple - a single mysql query could run every day and its done
• calculating the age is time consuming when you have lots of page views - amount of times its viewed far outweighs the number of changes

Just imagine a table scenario - a table with 100 or 1000 rows that shows the age of a person ... how long is that going to take to compute ???

I always thought that Stackoverflow calculated the Reputation dynamically but you can see on the Stackoverflow data explorer that they dont - see the User object in the schema on the right. Its recorded and updated each time its changed - I would guess that this is purely because of the amount of times its viewed far outweighs the number of changes

回答5:

I don't think it's totally true that computing age dynamically takes a lot of memory. Why not create a table CALENDAR with 365 rows 1 row for each day of an year. And store a list of userid against the day corresponding to their birthday. For each day just refer the table entry for that day and refresh the age of only those selected users. This will reduce the complexity greatly even when the user base increases.