从出生日期起用小数点计算年龄 [英] Calculate age with decimals from date of birth

问题描述

我的MySQL表中有一个dob字段，其类型为date.只是一个简单的小例子，就像这样:

I have a dob field in my MySQL table that's of type date. Just a small, trivial example is something like this:

mysql> select dob from players limit 5;
+------------+
| dob        |
+------------+
| 1983-12-02 |
| 1979-01-01 |
| 1989-05-11 |
| 1976-03-24 |
| 1989-09-12 |
+------------+

我正在尝试通过使用今天的日期来计算带小数点的年龄.因此，从技术上讲，如果您的生日是1981年6月1日或1981-06-01，那么您33岁，今天是6月7日.您会33.(6/365) or 33.02 years old.使用SQL进行计算的最简单方法是什么?

I am trying to calculate ages with decimal points by using today's date. So technically if your birthday is June 1, 1981 or 1981-06-01, that makes you 33 and today is June 7.. you'd be 33.(6/365) or 33.02 years old. What's the easiest way to calculate this using SQL?

推荐答案

通常，当您想在没有小数部分的情况下计算年份时，在mysql中DOB计算通常很容易

Usually DOB calculation is pretty easy in mysql when you want to calculate the years without any fraction something as

mysql> select timestampdiff(YEAR,'1981-06-01',now());
+----------------------------------------+
| timestampdiff(YEAR,'1981-06-01',now()) |
+----------------------------------------+
|                                     33 |
+----------------------------------------+

但是由于您也需要分数，因此应该可以解决问题

But since you need the fraction also then this should do the trick

mysql> select format(datediff(curdate(),'1981-06-01') / 365.25,2);
+-----------------------------------------------------+
| format(datediff(curdate(),'1981-06-01') / 365.25,2) |
+-----------------------------------------------------+
| 33.02                                               |
+-----------------------------------------------------+

年份被认为是365.25天.

Year is considered as 365.25 days.

因此，在您的情况下，您的查询可能为

So in your case you may have the query as

select 
format(datediff(curdate(),dob) / 365.25,2) as dob 
from players limit 5;

这篇关于从出生日期起用小数点计算年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！