以整数形式获取 MySQL 中两个日期之间的年差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10506731/
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
Get difference in years between two dates in MySQL as an integer
提问by Ramy Al Zuhouri
I am trying to calculate how old is a person in a database.
Let's suppose to have this simple table:
我想计算一个人在数据库中的年龄。
让我们假设有这个简单的表：
student(id, birth_date);
Where id is the primary key (truly the table is more complex but I have simplified it).
I want to get how much old is a single person:
其中 id 是主键（实际上该表更复杂，但我已对其进行了简化）。
我想知道一个人的年龄：
select id, datediff(curdate(),birth_date)
from student
But it returns a result in days, and not in years.I could divide it by 365:
但它以天为单位返回结果，而不是以年为单位。我可以将其除以 365：
select id, datediff(curdate(),birth_date) / 365
from student
But it returns a floating point value, and I want an integer.
So I could calculate the years:
但它返回一个浮点值，我想要一个整数。
所以我可以计算年份：
select id, year(curdate())-year(birth_date)
from student
But there is a problem: for instance now is May, if a person war born in June, 1970 he has still 31 years and not 32, but the expression returns 32.
I can't come out of this problem, can someone help me?
但是有一个问题：比如现在是5月，如果一个人出生于1970年6月，他还有31岁而不是32岁，但是表达式返回32。
我无法解决这个问题，有人可以帮我吗?
回答by Rat-a-tat-a-tat Ratatouille
For anyone who comes across this:
对于遇到此问题的任何人：
another way this can be done is:
另一种方法是：
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS difference FROM student
For differences in months, replace YEARwith MONTH, and for days replace YEARwith DAY
对于月份的差异，替换YEAR为MONTH，对于天数替换YEAR为DAY
Hope that helps!
希望有帮助！
回答by Scott Bonner
select id, floor(datediff(curdate(),birth_date) / 365)
from student
What about flooring the result to be an integer?
将结果地板化为整数怎么样？
回答by elias
Try:
尝试：
SELECT
DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birth_date)), '%Y')+0
AS age FROM student;
回答by Cyril N.
The accepted answer is almost correct, but can lead to wrong results.
接受的答案几乎是正确的，但可能会导致错误的结果。
In fact, / 365doesn't take into consideration leap years, and can lead to falsy results if you compare a date that has the same day&month than the birthdate.
事实上，/ 365没有考虑闰年，如果你比较一个日期和月份与生日相同的日期，可能会导致错误的结果。
In order to be more accurate, you have to divide it by the average days in years for 4 years, aka (365 * 4) + 1 (the leap year every 4 years) => 365.25
为了更准确，您必须将其除以 4 年的平均天数，即 (365 * 4) + 1（每 4 年的闰年）=> 365.25
And you will be more accurate :
你会更准确：
select id, floor(datediff(curdate(),birth_date) / 365.25) from student
Tested for one of my project and it's working.
对我的一个项目进行了测试，它正在工作。
回答by Harper Shelby
Why not use MySQL's FLOOR() function on the output from your second approach? Any fractions will be dropped, giving you the result you want.
为什么不在第二种方法的输出上使用 MySQL 的 FLOOR() 函数？任何分数都将被删除，给你你想要的结果。
回答by jams
You can use this to get integer value.
您可以使用它来获取整数值。
select id, CAST(datediff(curdate(),birth_date) / 365 as int)
from student
If you want to read about CONVERT()and CAST()here is the link.
如果你想阅读CONVERT()，CAST()这里是链接。
回答by Marek Dem?ák
I'd suggest this:
我建议这样做：
DATE_FORMAT(NOW(),"%Y")
-DATE_FORMAT(BirthDate,'%Y')
-(
IF(
DATE_FORMAT(NOW(),"%m-%d") < DATE_FORMAT(BrthDate,'%m-%d'),
1,
0
)
) as Age
This should work with leap-years very well ;-)
这应该很好地适用于闰年;-)
回答by Steve Burke
This works, even taking in account leap years!
这有效，即使考虑到闰年！
select floor((cast(date_format('2016-02-29','%Y%m%d') as int) - cast(date_format('1966-03-01','%Y%m%d') as int)/10000);
Just be sure to keep the floor()as the decimal is not needed
请务必保留，floor()因为不需要小数
回答by Aleksey Kuznetsov
I have not enough reputation to add comment to an answer by Rat-a-tat-a-tat Ratatouille to improve his code. Here is the better SQL-query:
我没有足够的声誉来为 Rat-a-tat-a-tat Ratatouille 的答案添加评论以改进他的代码。这是更好的 SQL 查询：
SELECT IFNULL(TIMESTAMPDIFF(YEAR, birthdate, CURDATE()), YEAR(CURDATE()) - YEAR(birthdate)) AS age
This is better because sometimes "birthdate" may contain only year of birth, while day and month is 0. If TIMESTAMPDIFF() returns NULL, we can find rough age by subtracting the current year from the year of birth.
这更好，因为有时“生日”可能只包含出生年份，而日和月为 0。如果 TIMESTAMPDIFF() 返回 NULL，我们可以通过从出生年份中减去当前年份来得出粗略的年龄。
