Compare birth date registered in the database to a custom date - mysql

Question

Good afternoon, I am displaying a report, where it is to show customers who own + 60 years. Like me nay own a field idade in the comic book, I had to "discover it" by birth date and did so:

SELECT TIMESTAMPDIFF(YEAR, data_de_nascimento, CURDATE()) as idade FROM clientes

Now I need to specify on select, that I just want you to show who’s over 60.

I tried to put WHERE idade >= 60 but it didn’t work.

I’m trying to compare to date of birth with the year of 1960 (2020 - 1960 = 60), but I want to take the day and the current month, so I don’t have to keep switching and have a "flawless" search, but I’m not getting... I’m trying this way:

WHERE data_de_nascimento <= '1960-Month(data)-Day(now))'

Someone can help me?

Answer 1

Straight to the point, the correct way to query is to repeat the same formula you used for the age column, thus staying:

SELECT TIMESTAMPDIFF(YEAR, data_de_nascimento, CURDATE()) as idade 
FROM clientes
WHERE TIMESTAMPDIFF(YEAR, data_de_nascimento, CURDATE()) >= 60

An Online Example Consultation Over 60

Example Code: (if no click)

create table Test(id integer, nome varchar(100), nascimento datetime);
insert into Test(id, nome, nascimento) values(1, "Joaquim", '1960-02-15'); -- 60 anos de idade
insert into Test(id, nome, nascimento) values(1, "Manel", '1960-04-05');   -- 59 anos de idade
insert into Test(id, nome, nascimento) values(1, "Joana", '1957-08-25');   -- 62 anos de idade
insert into Test(id, nome, nascimento) values(1, "Maria", '1961-11-07');   -- 58 anos de idade
select id, nome, nascimento, TIMESTAMPDIFF(YEAR, nascimento, CURDATE()) as idade from Test
where TIMESTAMPDIFF(YEAR, nascimento, CURDATE()) >= 60;

Mysql works with Alias for the fields, but by the time you validate the Where clause, the field may be without a given value. For this reason there is this restriction on using Alias in Where.

Source: Mysql 8.0 Reference Manual: Field Alias Problems

Answer 2

As commented in another reply Mysql uses alias for the fields but does not allow references to them in WHERE. However, you can use a clause HAVING. HAVING will only be evaluated after the calculation has already been performed and idade will already be available.

SELECT TIMESTAMPDIFF(YEAR, data_de_nascimento, CURDATE()) as idade FROM clientes HAVING idade >= 60;

Answer 3

Make sure this is what you want:

SELECT * FROM clientes WHERE data_de_nascimento <= DATE_SUB(CURDATE(), INTERVAL 60 YEAR);