[MySQL] TIMESTAMPDIFF의 사용

Table = "patients"

 

1) SELECT gender, count(name) FROM patients GROUP BY gender;

2) SELECT count(name) FROM patients WHERE timestampdiff(YEAR,birth_date,curdate()) >= 40;

3) SELECT name FROM patients WHERE timestampdiff(YEAR,last_visit_date,curdate())>= 1;

4) SELECT count(name) FROM patients WHERE birth_date BETWEEN '1980-01-01' AND '1989-12-31';

 

2)번과 3)의 쿼리는 아래와 같이 변경할 수 있다.

2) SELECT COUNT(*) FROM patients WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 40 YEAR);

3) SELECT id, name, birth_date, gender, last_visit_date FROM patients WHERE last_visit_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);