MySQL MySQL中的日期差异计算年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1383516/
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
Date Difference in MySQL to calculate age
提问by chsab420
I have a problem regarding the datediffMYSQL function, I can use it and it is simple. But I don't understand how to use it to collect differences within the table field. E.g.
我有一个关于datediffMYSQL 函数的问题，我可以使用它并且很简单。但我不明白如何使用它来收集表字段内的差异。例如
I have a column doband I want to write a query that will do something like
我有一列dob，我想写一个查询，它会做类似的事情
select dateDiff(current_timeStamp,dob)
from sometable 'here dob is the table column
I mean I want the difference from the current date time to the table field dob, each query result is the difference, the age of the user.
我的意思是我想要从当前日期时间到表字段dob的差异，每个查询结果是差异，用户的年龄。
回答by Nadia Alramli
回答by Sunday Ikpe
You could do this
你可以这样做
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) ASageFROM your_table
SELECT TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) AS年龄FROM your_table
Works everytime.
每次都有效。
回答by Martin v. L?wis
If you want, for each user, display the age in years, do
如果您想为每个用户显示年龄（以年为单位），请执行
select name,extract(year from (from_days(dateDiff(current_timestamp,dob))))
from sometable;
回答by Ken Keenan
If I understand your comments on the previous answers, the date-of-birth column is not actually a DATEvalue but a string in the format m/d/y. I stronglyrecommend you change this; it slows down any date computations you want to do and you risk invalid date values getting entered into the column.
如果我理解您对之前答案的评论，那么出生日期列实际上不是一个DATE值，而是一个格式为 m/d/y 的字符串。我强烈建议你改变这个；它会减慢您想要进行的任何日期计算，并且您冒着将无效日期值输入到该列中的风险。
I think this is what you need. It uses the STR_TO_DATE()function and an algorithm for computing the age from the MySQL documentation:
我认为这就是你所需要的。它使用STR_TO_DATE()函数和算法来计算 MySQL 文档中的年龄：
SELECT YEAR(CURDATE()) - YEAR(STR_TO_DATE(dob, '%m/%d/%Y'))
- (RIGHT(CURDATE(), 5) < RIGHT(STR_TO_DATE(dob, '%m/%d/%Y'), 5)) AS age
FROM sometable;
回答by VISHNU
I think this should help
我认为这应该有帮助
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
Note: Give the D.O.B in the correct format, E.g. YYYY-MM-DD'=> '1991-11-11
注意：以正确的格式提供 DOB，例如 YYYY-MM-DD'=> '1991-11-11
回答by Akhileshwar Reddy
Try this
尝试这个
SELECT DATEDIFF(CURDATE(), '2014-02-14');
回答by David S Gonzalez H
select truncate(datediff(curdate(),dob)/365.25,0) from table;
