2011年12月29日星期四

在 MySQL 計算年齡 (Calculating Age in MySQL)

儲存個人資料時,由於年齡是一種跟隨時間而變動的資料,因此合理情況上不會儲存年齡
取而代之會以出生日期為儲存資料,便可以透過日期相減計算出年齡
When recording an user data, age is an dynamic data. Generally, you should not store it.
So you store the date of birth, then calculating the age from date of birth.

一般而言,在大部分情況下都只以年距相減來簡單計算年齡資料
Normally, in most cases, subtract from now to the year of date of birth data for a simple calculation.
(YEAR(NOW()) - YEAR(`date_of_birth`)) AS `age`

這種計算方法比較危險
例如出生日期為 1990-12-31,現在為 2010-1-1 ,若果只以年距相減,年齡為 20歲
但實際情況只是 19歲,因為直到 2010-12-31 才是 20歲
要得到更加準確的年齡計算需要使用 FROM_DAYS 及 DATEDIFF
This calculating method is danger.
For example the date of birth is 1990-12-31, current date is 2010-1-1.
Subtract from now to the year of date of birth is 20 years old.
However, the real age is 19 years old. It is because 20 years old until 2010-12-31.
To get a more accurate calculation of the age required FROM_DAYS and DATEDIFF
FROM_DAYS(DATEDIFF(NOW(), `date_of_birth`)) AS `age`

這樣,剛才例子便會得到 0019-01-02 ,一個比較準確的數值 (不建議嚴緊至計算時間)
So, the example gets 0019-01-02, it is a more accurate value (Unnecessary calculate with time)

沒有留言 :

發佈留言