在MySQL中，以整数形式获得两个日期之间的年差

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?

但是有一个问题:比如现在是五月，如果一个人出生在1970年6月，他还有31年而不是32年，但是这个表达式返回32。我解决不了这个问题，有人能帮我吗?

9 个解决方案

#1

select id, floor(datediff(curdate(),birth_date) / 365)
from student

What about flooring the result to be an integer?

用地板把结果变成整数怎么样?

#2

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 YEAR with MONTH, and for days replace YEAR with DAY

为了几个月的差异，用月代替年，用天数代替年。

Hope that helps!

希望会有帮助!

#3

Try:

试一试:

SELECT 
  DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birth_date)), '%Y')+0
  AS age FROM student;

#4

The accepted answer is almost correct, but can lead to wrong results.

公认的答案几乎是正确的，但可能导致错误的结果。

In fact, / 365 doesn'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

为了更准确，你需要将它除以四年的平均天数，即(365 * 4)+ 1(每四年闰年)=> 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.

测试了我的一个项目，它正在运行。

#5

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()函数呢?任何分数都会被删除，得到你想要的结果。

#6

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.

如果您想了解转换()和CAST()，这里是链接。

#7

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 ;-)

这应该很好地配合跨年工作;

#8

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()，因为不需要小数

#9

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:

我没有足够的声誉去对rata -a-tat- tat- tat- tat- tat- tat- tat- 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，我们可以通过从出生年份中减去当前年份来找到粗略的年龄。

#1

select id, floor(datediff(curdate(),birth_date) / 365)
from student