Calculate age in MySQL (InnoDB)

If I have a person's date of birth stored in a table in the form dd-mm-yyyy, and I subtract it from the current date, what format is the date returned in?

How开发者_JAVA百科 can I use this returned format to calculate someone's age?

You can use TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) function:

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

Demo

If the value is stored as a DATETIME data type:

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

Less precise when you consider leap years:

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

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

Check the demo image below

Simple but elegant..

Use:

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

In this way, you consider even month and day of birth in order to have a more accurate age calculation.

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

Simply:

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

Since the question is being tagged for mysql, I have the following implementation that works for me and I hope similar alternatives would be there for other RDBMS's. Here's the sql:

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

Try this:

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);

It returns this year - birth year (how old the person will be this year after the birthday) and adjusts based on whether the person has had the birthday yet this year.

It doesn't suffer from the rounding errors of other methods presented here.

^{Freely adapted from here}

Simply do

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

birthdate is field name that keep birthdate name take CURDATE() turn to year by YEAR() command minus with YEAR() from the birthdate field

This is how to calculate the age in 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

You can make a function to do it:

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 ;

Now, you can get the age from a date:

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

If you date is in format Y-m-d H:i:s, you can do this:

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

You can reuse this function anywhere ;)

I prefer use a function this way.

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 ;

now to use

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

OR

SELECT F_AGE(date) AS age FROM table;

There is two simple ways to do that:

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"))