如何从 MySQL 的 DOB 字段中获取年龄？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2533890/
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
How to get an age from a D.O.B field in MySQL?
提问by aadersh patel
I need to calculate the age of a "customer" from their date of birth.
我需要从他们的出生日期计算“客户”的年龄。
I have tried to use the following:
我尝试使用以下内容：
DATEDIFF(year, customer.dob, "2010-01-01");
DATEDIFF（年份，customer.dob，“2010-01-01”）；
But it does not seem to work.
但它似乎不起作用。
Any ideas? I KNOW it is going to be something simple!
有任何想法吗？我知道这会很简单！
Thanks
谢谢
回答by Bryan Denny
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
回答by almaruf
Use Mysql recommended :
推荐使用Mysql：
TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age;
Usage in a query :
查询中的用法：
SELECT name, dob, TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age FROM pet;
Ref: http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html
参考：http: //dev.mysql.com/doc/refman/5.0/en/date-calculations.html
回答by Marcos Placona
A few ways:
几种方式：
select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y')+0 AS age
Hope this helps you
希望这对你有帮助
回答by tokes
Bryan Denny's answer is more correct than the accepted answer(I wasn't sure how to put this in somewhere other than a new answer; this is my first time on StackOverflow).
Bryan Denny 的答案比接受的答案更正确（我不确定如何将它放在新答案以外的其他地方；这是我第一次使用 StackOverflow）。
Marcos' first attempt:
马科斯的第一次尝试：
select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age
will firstly yield a negative result (the arguments to DATEDIFF are in the wrong order), and secondly will produce inaccurate results for some dates, e.g.:
将首先产生否定结果（DATEDIFF 的参数顺序错误），其次会产生某些日期的不准确结果，例如：
SELECT DATEDIFF('2010-05-11','1984-05-11') / 365.25 AS age
produces the result:
产生结果：
25.9986
You can't just simply always round up, because that will also cause inaccurate results for other inputs.
您不能总是简单地四舍五入，因为这也会导致其他输入的结果不准确。
Marcos' second attempt:
马科斯的第二次尝试：
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y')+0 AS age
Again, the arguments are in the wrong order, except this time instead of just producing a negative number, the FROM_DAYS() function does not work correctly with negative input. Secondly, if we look closer at the output of the FROM_DAYS() function:
同样，参数的顺序是错误的，除了这次不是只产生负数，FROM_DAYS() 函数在负输入时不能正常工作。其次，如果我们仔细观察 FROM_DAYS() 函数的输出：
select from_days(datediff('2010-09-16','1984-05-11'));
The result of the above is:
上面的结果是：
0026-05-08
which is literally "8th of May, Year 26 (after 0)". Keep in mind that for datetime types, there is no month "0", so if you wanted to use this format to measure a date interval with months included, you'd have to subtract 1 from the month. Similarly, with the day component, there is no "0", so the result is not what you'd expect for this problem when the date happens to be the birthday:
字面意思是“5 月 8 日，26 年（0 之后）”。请记住，对于日期时间类型，没有月份“0”，因此如果您想使用此格式来测量包含月份的日期间隔，则必须从月份中减去 1。同样，对于日期组件，没有“0”，因此当日期恰好是生日时，结果不是您对此问题所期望的：
select from_days(datediff('2010-05-11','1984-05-11'));
produces:
产生：
0025-12-31
which if we shorten using Marcos' date formatting gives us "25", which is an incorrect calculation of age.
如果我们使用 Marcos 的日期格式来缩短它会得到“25”，这是一个不正确的年龄计算。
Bryan Denny's answer is correct in all these edge cases. His formula is quite clever:
Bryan Denny 的回答在所有这些边缘情况下都是正确的。他的公式很巧妙：
SELECT DATE_FORMAT(reference, '%Y') - DATE_FORMAT(birthdate, '%Y') - (DATE_FORMAT(reference, '00-%m-%d') < DATE_FORMAT(birthdate, '00-%m-%d')) AS age
The first part calculates the difference in years between the two dates. So if we take "2010" and "1984" as reference and birthdate respectively, the result is "26". The second part then calculates essentially "Does the birthdate month and day occur after the reference month and day?" If it does, it "hasn't happened yet", so we need to subtract an additional 1 from the year difference to make up for this. This is taken care of by the result of the < comparison, which returns 1 if true and 0 if false.
第一部分计算两个日期之间的年差。所以如果我们分别以“2010”和“1984”作为参考和生日，结果是“26”。然后第二部分基本上计算“生日月份和日期是否出现在参考月份和日期之后？” 如果是，则“尚未发生”，因此我们需要从年份差异中减去 1 以弥补这一点。这由 < 比较的结果处理，如果为真则返回 1，如果为假则返回 0。
So, full examples:
所以，完整的例子：
1)
1)
Reference date: 2010-05-10;
Birthdate: 1984-05-11
Year difference = 2010 - 1984 = 26
Month and day comparison: May 10th < May 11th? Yes => subtract an additional year
Calculated age: 25 years
2)
2)
Reference date: 2010-05-11;
Birthdate: 1984-05-11
Year difference = 2010 - 1984 = 26
Month and day comparison: May 11th < May 11th? No => subtract 0
Calculated age: 26 years
I hope this makes things clearer for people!
我希望这能让人们更清楚！
回答by Ipsita Rout
The below sql works fine for me. Always use CURRENT_DATEwith dob to calculate the actual age .
下面的 sql 对我来说很好用。始终CURRENT_DATE与 dob 一起使用来计算实际年龄。
SELECT
DATE_FORMAT(
FROM_DAYS(
DATEDIFF(CURRENT_DATE, dob)
),
'%y Years %m Months %d Days'
) AS age
FROM
users
回答by Sapna Bhayal
SELECT *, YEAR(CURDATE()) - YEAR(birthdate) AS age FROM user;
I found some useful queries from below article for calculating age :- http://www.gizmola.com/blog/archives/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html
我从下面的文章中找到了一些用于计算年龄的有用查询：- http://www.gizmola.com/blog/archives/archives/107-Calculate-a-persons-age-in-a-MySQL-query.html
回答by Ali A. Dhillon
If you want years, months and days like "26 years 4 months and 27 days" then following is the age function for you:
如果您想要像“26 年 4 个月零 27 天”这样的年、月和日，那么以下是适合您的年龄函数：
delimiter $$
create function age(dob date)
returns varchar(30)
deterministic
begin
declare years int default 0;
declare months int default 0;
declare days int default 0;
declare today date default curdate();
set years = timestampdiff(year, dob, today);
set months = timestampdiff(month, dob, today - interval years year);
set days = timestampdiff(day, dob, today - interval years year - interval months month);
return (concat(years, ' years ', months,' months and ', days,' days'));
end$$
delimiter;
We are calculating the age from current date so I set today to curdate(). Now we are getting our years, months and days as following.
我们正在计算当前日期的年龄，所以我今天设置为 curdate()。现在我们的年、月和日如下。
years:We get the difference in years from today to date of birth.
months:First we subtract the years from the today so that we only get months that are extra from the years and then find the difference in months from date of birth. Here "years" is the variable and "year" is interval type.
- days:And here we subtract years and months that we have already calculated from today which is current date then find the diff in days from birth. Here "months" is the variable and "month" is interval type.
年：我们得到从今天到出生日期的年数差异。
月数：首先我们从今天减去年数，这样我们只得到年数中额外的月数，然后找出从出生日期算起的月数差异。这里“years”是变量，“year”是区间类型。
- 天数：在这里我们减去我们已经从今天（当前日期）计算的年数和月数，然后找到从出生开始的天数差异。这里“months”是变量，“month”是区间类型。
You can use datetime instead of date as argument if you want also you can format the return values as you like.
如果需要，您可以使用 datetime 而不是 date 作为参数，也可以根据需要格式化返回值。
回答by S.Mishra
DATE_FORMAT(FROM_DAYS(DATEDIFF(CURDATE(),'1869-10-02')), '%Y')+0 AS age;
Above MySQL query has been tested and verified. It will give you exact age in years. I have taken this idea from Marcos answer and switched DATEDIFF() parameters.
以上MySQL查询已经过测试验证。它会给你准确的年龄。我从 Marcos 的回答中汲取了这个想法并切换了 DATEDIFF() 参数。
回答by user1218947
Depends on your needs- int and float functions provided.
- Your business rules may differ so adjust accordingly
取决于您的需要- 提供 int 和 float 函数。
- 您的业务规则可能有所不同，因此请相应调整
DROP FUNCTION IF EXISTS `age`;
CREATE FUNCTION `age` (
`pdate_begin` DATE,
`pdate_end` DATETIME
) RETURNS INT(11) UNSIGNED
COMMENT 'Calc age between two dates as INT'
DETERMINISTIC NO SQL SQL SECURITY DEFINER
RETURN floor(datediff(pdate_end, pdate_begin) / 365.25) ;
DROP FUNCTION IF EXISTS `age_strict`;
CREATE FUNCTION `age_strict` (
`pdate_begin` DATE,
`pdate_end` DATETIME
) RETURNS decimal(10,4)
COMMENT 'Calc age between two dates as DECIMAL .4'
DETERMINISTIC NO SQL SQL SECURITY DEFINER
RETURN round(datediff(pdate_end, pdate_begin) / 365.25, 4) ;
-- test harness
select
age(dob, now()) as age_int,
age_strict(dob, now()) as age_dec
from customer
where dob is not null
order by age(dob,now()) desc;
-- test results
dob, age_int, age_dec
1981-01-01 00:00:00 33 33.9713
1987-01-09 00:00:00 27 27.9507
2014-11-25 00:00:00 0 0.0739
回答by Ravi Shankar
DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`_AGE` $$
CREATE FUNCTION `_AGE`(in_dob datetime) RETURNS VARCHAR(100)
NO SQL
BEGIN
DECLARE l_age VARCHAR(100);
DECLARE YEARS INT(11);
DECLARE MONTHS INT(11);
DECLARE DAYS INT(11);
DECLARE DIFFS FLOAT;
SET DIFFS=DATEDIFF(CURRENT_DATE(),in_dob) /365.25;
SET YEARS=FLOOR(DIFFS) ;
SET MONTHS=FLOOR((DIFFS - YEARS)*365.25/30.4375) MOD 12;
SET DIFFS=((DIFFS - YEARS)*365.25/30.4375);
SET DAYS=CEIL(((DIFFS-MONTHS)*30.4375)) MOD 31;
SET l_age=CONCAT(YEARS, " Year ",MONTHS," Month ",DAYS," Days");
RETURN(l_age);
END $$
DELIMITER ;
SELECT _Age(CAST('1980-07-16' AS DATE));
