I can create a table in mysql that shows the person’s age by subtracting today’s year - her birth year?

Asked

Viewed 146 times

0

Question: I can create a table in mysql that shows the person’s age by subtracting today’s year minus her birth year?

   create table idade2 (ID auto_increment Primary key, data_nasc date, idade int as ((year(curdate() - year(data_nasc)) stored);

It may be so?

mysql sql database

  • It’s no use just subtracting the year. If a person was born in December 2000, in January 2018 he is not yet 18 years old (which is the result you would get if you just subtracted the years). You should take into account the month and day as well. For difference of dates, use TIMESTAMPDIFF or DATEDIFF

  • Another detail about age calculation is related to leap years: if someone was born on February 29, 2000, for example. On February 28, 2018, is this person 18 years old? Or only on March 1? (since there is no February 29 in 2018). Some Apis consider that on 02/28/2018 the person has already turned 18, others consider that only on March 1. I do not know what TIMESTAMPDIFF and DATEDIFF do in such cases, but it’s an interesting test to do.

  • Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

1 answer

0

The best would be for you to bring age into one SELECT as follows:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(data_nasc) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(data_nasc, 5)) as idade
  FROM TABELA

It is not very good to save calculation attributes in tables unless you have to do it.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.