以整数形式获取 MySQL 中两个日期之间的年差 [英] Get difference in years between two dates in MySQL as an integer

问题描述

我正在尝试计算一个人在数据库中的年龄.
让我们假设有这个简单的表:

student(id,birth_date);

其中 id 是主键(确实该表更复杂，但我已将其简化).
我想知道一个人的年龄:

select id, datediff(curdate(),birth_date)来自学生

但它以天为单位返回结果，而不是以年为单位.我可以将其除以 365:

select id, datediff(curdate(),birth_date)/365来自学生

但它返回一个浮点值，我想要一个整数.
所以我可以计算年份:

select id, year(curdate())-year(birth_date)来自学生

但是有一个问题:比如现在是五月，如果一个人战争出生于1970年六月，他还有31岁而不是32岁，但表达式返回32.
我无法解决这个问题，有人可以帮助我吗?

解决方案

对于遇到此问题的任何人:

另一种方法是:

SELECT TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) 作为学生的差异

对于月份的差异，将YEAR替换为MONTH，对于天数，将YEAR替换为DAY

希望有帮助！

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:

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:

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?

解决方案

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!

这篇关于以整数形式获取 MySQL 中两个日期之间的年差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！