在 MySQL (InnoDb) 中计算年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5773405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate Age in MySQL (InnoDb)
提问by user559142
If I have a persons date of birth stored in a table in the form dd-mm-yyyyand I subtract it from the current date, what format is the date returned in?
如果我将一个人的出生日期存储在表格中的表格中dd-mm-yyyy，然后从当前日期中减去它，那么返回的日期格式是什么？
How can I use this returned format to calculate someone's age?
如何使用此返回格式来计算某人的年龄？
采纳答案by OMG Ponies
If the value is stored as a DATETIME data type:
如果值存储为 DATETIME 数据类型：
SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age
FROM YOUR_TABLE
Less precise when you consider leap years:
考虑闰年时不太精确：
SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
FROM YOUR_TABLE t
回答by Glavi?
You can use TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)function:
您可以使用TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)功能：
SELECT TIMESTAMPDIFF(YEAR, '1970-02-01', CURDATE()) AS age
回答by Nicola Cossu
select *,year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)) as age from your_table
in this way you consider even month and day of birth in order to have a more accurate age calculation.
通过这种方式，您甚至可以考虑出生的月份和日期，以便更准确地计算年龄。
回答by Ferd Shinoda
SELECT TIMESTAMPDIFF (YEAR, YOUR_COLUMN, CURDATE()) FROM YOUR_TABLE AS AGE
点击演示..
Simple but elegant..
简单但优雅..
回答by lobster1234
select floor(datediff (now(), birthday)/365) as age
回答by Damonsson
Simply:
简单地：
DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`birthDate`)), '%Y')+0 AS age
回答by mickeymoon
Since the question is being tagged for mysql, I have the following implementation that works for me and I hope similar alternatives would be there for other RDBMS's. Here's the sql:
由于问题被标记为mysql，我有以下对我有用的实现，我希望其他 RDBMS 也有类似的替代方案。这是sql：
select YEAR(now()) - YEAR(dob) - ( DAYOFYEAR(now()) < DAYOFYEAR(dob) ) as age
from table
where ...
回答by Sklivvz
Try this:
尝试这个：
SET @birthday = CAST('1980-05-01' AS DATE);
SET @today = CURRENT_DATE();
SELECT YEAR(@today) - YEAR(@birthday) -
(CASE WHEN
MONTH(@birthday) > MONTH(@today) OR
(MONTH(@birthday) = MONTH(@today) AND DAY(@birthday) > DAY(@today))
THEN 1
ELSE 0
END);
It returns this year - birth year (how old the person will be this year after the birthday) and adjusts based on whether the person has had the birthday yet this year.
它返回今年 - 出生年份（生日后今年的年龄）并根据此人今年是否过生日进行调整。
It doesn't suffer from the rounding errors of other methods presented here.
它不会受到此处介绍的其他方法的舍入误差的影响。
Freely adapted from here
从这里自由改编
回答by Kuzmiraun
Simply do
简单地做
SELECT birthdate, (YEAR(CURDATE())-YEAR(birthdate)) AS age FROM `member`
birthdate is field name that keep birthdate name take CURDATE() turn to year by YEAR() command minus with YEAR() from the birthdate field
出生日期是保留出生日期名称的字段名称，通过 YEAR() 命令将 CURDATE() 转为年份，从生日字段中减去 YEAR()
回答by Amine_Dev
There is two simples ways to do that :
有两种简单的方法可以做到这一点：
1-
1-
select("users.birthdate",
DB::raw("FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(users.birthdate, '%Y-%m-%d'))/365) AS age_way_one"),
2-
2-
select("users.birthdate",DB::raw("(YEAR(CURDATE())-YEAR(users.birthdate)) AS age_way_two"))
