以年月日为单位计算年龄 [英] calculate age in years months and days

问题描述

我在mysql中使用FROM_DAYS函数时遇到问题，我想从my_table获取年龄，这是我的查询；

I got problem using FROM_DAYS function in mysql, i want to get age from my_table, here's my query;

select dob,CURRENT_DATE,
DATE_FORMAT(FROM_DAYS(DATEDIFF(CURRENT_DATE,dob)),'%y yr %c mth %e dy') AS age, 
DATEDIFF(CURRENT_DATE,dob) days from my_table

结果:

dob       || CURRENT_DATE || age              || days  ||
==========++==============++==================++=======++
1953-09-10|| 2013-09-12   || 60 yr 1 mth 3 dy || 21917 ||
2013-09-08|| 2013-09-12   || 00 yr 0 mth 0 dy ||     4 ||

当我为第二行尝试FROM_DAYS(DATEDIFF(CURRENT_DATE,m.tgllahir))时，这是结果0000-00-00

when I tried FROM_DAYS(DATEDIFF(CURRENT_DATE,m.tgllahir)) for 2nd row this is the result 0000-00-00

，然后我尝试查看日期和时间函数，并找到了

and then I tried looking at Date and Time Functions and found this

SELECT FROM_DAYS(730669);
        -> '2007-07-03'

但是当我尝试时，这就是我得到的；

but when I tried this is what I got;

SELECT FROM_DAYS(730669);
        -> '2000-07-03'

我很好奇为什么FROM_DAYS功能不能正常工作，但是我的主要问题是如何找到年龄?

I'm curious why FROM_DAYS function not working properly, but my main problem is how to find the age?

编辑

与Barmar讨论之后，我知道我无法使用FROM_DAYS来获取准确的年龄，因此我尝试寻找其他方法

after a discussion with Barmar I know that I can't use FROM_DAYS to get accurate age, so I tried to find different method

推荐答案

SELECT FLOOR(( DATE_FORMAT(NOW(),'%Y%m%d') - DATE_FORMAT(t.dob,'%Y%m%d'))/10000) AS years,
       FLOOR((1200 + DATE_FORMAT(NOW(),'%m%d') - DATE_FORMAT(t.dob,'%m%d'))/100) %12 AS months,
       (SIGN(DAY(NOW()) - DAY(t.dob))+1)/2 * (DAY(NOW()) - DAY(t.dob)) +
      (SIGN(DAY(NOW()) - DAY(t.dob))+1)/2 * (DAY(NOW()) - DAY(t.dob)) +
      (SIGN(DAY(t.dob) - DAY(NOW()))+1)/2 * (DAY(STR_TO_DATE(DATE_FORMAT(t.dob + INTERVAL 1 MONTH,'%Y-%m-01'),'%Y-%m-%d') - INTERVAL 1 DAY) - DAY(t.dob) + DAY(NOW())) AS days
 FROM tablename t

尝试此查询

这篇关于以年月日为单位计算年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！