转译自 How To Calculate Age From Date Of Birth In MySQL- Querychat。 中文转载,请注明出处。 使用SQL语句计算年龄,在事务处理和日期计算中,较为常见。MySQL提供了许多日期函数,可以自由发挥。本文中看我们尝试 MySQL没有开箱即用的工具,用于计算年龄。所以,这也阻挡不了我们求知的热情。工具不够,自己来凑。没有直接的函数,我们就用给的函数,组装出来一个。 说白了,年龄就是啷个日期差。当前日期,减去生日。那就是年龄。 MySQL已经提供的函数,下面介绍一下: 这俩函数就够了,日期差,获取年差值,月差值,或者其他。 CURDATE() 返回MySQL服务器运行时间。无需传参,调用如下: CURDATE() TIMESTAMPDIFF() 调用格式如下: 单位unit决定了返回数值。unit的可选项如下: 那,我们试着组合起来,写个公式: 上述SQL语句运行后返回 1. 修改一下参数: 返回的是 366 学习了两个函数的使用方法,下面计算两个日期相差的年份: 上式dob字段,在传递给TIMESTAMPDIFF函数之前,需要转化解析为日期格式字符串,才能正常使用。 当前日期是2019年12月20日, 假定生日是1978年8月3日,计算如下: 计算结果 41. 上面的式子,计算出了正确的年龄。但是如果日期格式不完整,缺少月份,或者日期,都不能计算出来结果。 对应同一个月份,或者同一年的日期,计算差值,年总是=0。因此我们提高精度: 计算两个日期差的月数。 以此类推: TIMESTAMPDIFF(DAY, dob_field, CURDATE()) 计算两个日期差的天数。 举一反三,我们可以相应的计算日期差的月份,和天数。 例如 TIMESTAMPDIFF( MONTH, dob_ field, CURDATE() ) % 12 计算月部分: TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 计算天部分: 组合在一起,就是完整的年龄表达式: SELECT CONCAT(TIMESTAMPDIFF( YEAR, dob_ field, CURDATE() ),' Years,', TIMESTAMPDIFF( MONTH, dob_ field, CURDATE()) % 12,' Months,',FLOOR( TIMESTAMPDIFF( DAY, dob_ field, CURDATE() ) % 30.4375 ),' Days') 其中dob_ field是包含生日的字段,或变量. 创建一张表,字段包含姓名,还有生日: 创建数据库 test_age,然后创建表 t1。 写入一些随机数据: 下面我们根据生日dob字段,分别计算并更新 age_year, age_month, age_days 和age_formatted。运行以下SQL语句: 更新完毕之后,查看结果 OK,一切结束,拿上SQL语句去数据库上测试一下吧。SQL 年龄计算 —— 组件
TIMESTAMPDIFF(unit, begin_date, end_date)
SELECT TIMESTAMPDIFF(YEAR, '2018-01-20', '2019-01-21');
SELECT TIMESTAMPDIFF(DAY, '2018-01-20', '2019-01-21');
SQL 计算年龄 —— 组合起来
TIMESTAMPDIFF(YEAR, dob, CURDATE())
SELECT TIMESTAMPDIFF(YEAR, '1978-08-03', CURDATE());
MySQL 计算年龄 —— 提高精度
TIMESTAMPDIFF(MONTH, dob_field, CURDATE())
举例说明
DROP TABLE IF EXISTS T1;CREATE TABLE T1(
student_name VARCHAR(100),
dob date,
age_year INT,
age_month INT,
age_days INT,
age_formatted VARCHAR(200)
);
INSERT INTO t1(student_name, dob) values ('Jennifer Brit', '1990-02-20');
INSERT INTO t1(student_name, dob) values ('David Dunkrick', '1988-12-20');
INSERT INTO t1(student_name, dob) values ('Michelle Morris', '2001-08-03');
INSERT INTO t1(student_name, dob) values ('Ubaid Ali', '1987-10-19');
INSERT INTO t1(student_name, dob) values ('Juliet Cross', '2019-01-25');
UPDATE T1SET
AGE_FORMATTED =
CONCAT(
TIMESTAMPDIFF( YEAR, DOB, CURDATE() ),
' YEARS,',
TIMESTAMPDIFF( MONTH, DOB, CURDATE()) % 12,
' MONTHS,',
FLOOR( TIMESTAMPDIFF( DAY, DOB, CURDATE() ) % 30.4375 ),
' DAYS'
),
AGE_YEAR = TIMESTAMPDIFF( YEAR, DOB, CURDATE() ),
AGE_MONTH = TIMESTAMPDIFF( MONTH, DOB, CURDATE()) % 12,
AGE_DAYS = FLOOR( TIMESTAMPDIFF( DAY, DOB, CURDATE()) % 30.4375);
SELECT * FROM T1;