以整数形式获取MySQL中两个日期之间的年份差异 [英] Get difference in years between two dates in MySQL as an integer
问题描述
我正在尝试计算数据库中一个人的年龄.
让我们假设有一个简单的表:
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);
其中id是主键(表实际上比较复杂,但我已经对其进行了简化).
我想让一个人多大年龄:
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
但是它返回的结果是几天而不是几年.我可以将其除以365:
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
但是有一个问题:例如现在是5月,如果一个人战争发生在1970年6月,那么他仍然是31岁而不是32岁,但表达式返回32.
我无法摆脱这个问题,有人可以帮助我吗?
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
对于月份差异,将YEAR
替换为MONTH
,对于几天,将YEAR
替换为DAY
For differences in months, replace YEAR
with MONTH
, and for days replace YEAR
with DAY
希望有帮助!
这篇关于以整数形式获取MySQL中两个日期之间的年份差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!