mysql - 计算MySQL中的年龄(InnoDb)

mysql - 计算MySQL中的年龄(InnoDb),第1张

如果我将某人的出生日期存储在dd-mm-yyyy形式的表格中,并且我从当前日期中减去该日期,则返回的日期是什么格式?

如何使用此返回格式计算某人的年龄?

最佳答案:

13 个答案:

答案 0 :(得分:209)

您可以使用TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)功能:

SELECT TIMESTAMPDIFF(YEAR, '1970-02-01', CURDATE()) AS age

<强> Demo

答案 1 :(得分:54)

如果值存储为DATETIME数据类型:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(dob) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(dob, 5)) as age 
  FROM YOUR_TABLE

考虑闰年时不太准确:

SELECT DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.birthday, '%d-%m-%Y'))/365 AS ageInYears
  FROM YOUR_TABLE t 

答案 2 :(得分:10)

select *,year(curdate())-year(dob) - (right(curdate(),5) < right(dob,5)) as age from your_table

通过这种方式,您可以考虑出生月份和日期,以便更准确地计算年龄。

答案 3 :(得分:7)

SELECT TIMESTAMPDIFF (YEAR, YOUR_COLUMN, CURDATE()) FROM YOUR_TABLE AS AGE

Click for demo ..

简单而优雅..

答案 4 :(得分:4)

select floor(datediff (now(), birthday)/365) as age

答案 5 :(得分:2)

简单地:

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`birthDate`)), '%Y') 0 AS age

答案 6 :(得分:1)

试试这个:

SET @birthday = CAST('1980-05-01' AS DATE);
SET @today = CURRENT_DATE();

SELECT YEAR(@today) - YEAR(@birthday) - 
  (CASE WHEN
    MONTH(@birthday) > MONTH(@today) OR 
    (MONTH(@birthday) = MONTH(@today) AND DAY(@birthday) > DAY(@today)) 
      THEN 1 
      ELSE 0 
  END);

它返回今年 - 出生年份(今年生日后这个人的年龄),并根据该人今年是否过生日进行调整。

它不会受到此处提供的其他方法的舍入误差的影响。

自由改编自here

答案 7 :(得分:1)

由于问题被标记为mysql,我有以下实现对我有用,我希望其他RDBMS可以使用类似的替代方案。这是sql

select YEAR(now()) - YEAR(dob) - ( DAYOFYEAR(now()) < DAYOFYEAR(dob) ) as age 
from table 
where ...

答案 8 :(得分:0)

这是如何计算MySQL中的年龄:

select
  date_format(now(), '%Y') - date_format(date_of_birth, '%Y') - 
  (date_format(now(), '00-%m-%d') < date_format(date_of_birth, '00-%m-%d'))
as age from table

答案 9 :(得分:0)

你可以做一个功能:

drop function if exists getIdade;

delimiter |

create function getIdade( data_nascimento datetime )
returns int
begin
    declare idade int;
    declare ano_atual int;
    declare mes_atual int;
    declare dia_atual int;
    declare ano int;
    declare mes int;
    declare dia int;

    set ano_atual = year(curdate());
    set mes_atual = month( curdate());
    set dia_atual = day( curdate());

    set ano = year( data_nascimento );
    set mes = month( data_nascimento );
    set dia = day( data_nascimento );

    set idade = ano_atual - ano;

    if( mes > mes_atual ) then
            set idade = idade - 1;
    end if;

    if( mes = mes_atual and dia > dia_atual ) then
            set idade = idade - 1;
    end if;

    return idade;
end|

delimiter ;

现在,您可以从日期开始获取年龄:

select getIdade('1983-09-16');

如果您的日期格式为Y-m-d H:i:s,则可以执行以下操作:

select getIdade(substring_index('1983-09-16 23:43:01', ' ', 1));

您可以在任何地方重复使用此功能;)

答案 10 :(得分:0)

我更喜欢以这种方式使用函数。

DELIMITER $$ DROP FUNCTION IF EXISTS `db`.`F_AGE` $$
    CREATE FUNCTION `F_AGE`(in_dob datetime) RETURNS int(11)
        NO SQL
    BEGIN
       DECLARE l_age INT;
       IF DATE_FORMAT(NOW(  ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN
          -- This person has had a birthday this year
          SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y');
        ELSE
          -- Yet to have a birthday this year
          SET l_age=DATE_FORMAT(NOW(  ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
       END IF;
       RETURN(l_age);
    END $$

    DELIMITER ;

现在使用

SELECT F_AGE('1979-02-11') AS AGE; 

OR

SELECT F_AGE(date) AS age FROM table;

答案 11 :(得分:0)

简单地做

SELECT birthdate, (YEAR(CURDATE())-YEAR(birthdate)) AS age FROM `member` 

birthdate是保留生日名称的字段名称 采用CURDATE()通过YEAR()命令转到年份 减去生日字段中的YEAR()

答案 12 :(得分:0)

有两种简单方法可以做到:

1 -

select("users.birthdate",
            DB::raw("FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(users.birthdate, '%Y-%m-%d'))/365) AS age_way_one"),

2 -

select("users.birthdate",DB::raw("(YEAR(CURDATE())-YEAR(users.birthdate)) AS age_way_two"))
本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复