Multiple ways to calculate age by birthday in MySQL

I didn't use MySQL very often before, and I wasn't very familiar with MySQL functions. When I encountered this problem, I searched on Baidu and found these two methods. These two methods were ranked first on Baidu's blog.

Method 1

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age

The author also points out the flaw in method 1, which is that when the date is a future date, the result is 0 instead of a negative number; 5 functions and two operators are used here.

Method 2

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age

Method 2 solves the problem of negative numbers in method 1, but it looks more complicated; 6 functions and 3 operators are used here.

After reading this post, I was confused. Why is it so complicated? It was very simple to use Sql Server before. I firmly believe that there must be a simple and efficient way. A method improved upon the above method was soon found.

Improved Method 1 and Method 2

SELECT year( from_days( datediff( now( ), birthdate)));
SELECT YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)<RIGHT(birthday,5));

The improved method 1 has one less function and one less operator. When the date is in the future, the calculation result is still 0.
The improved method 2 still has 6 functions and 3 operators, which looks simpler. When taking the five digits on the right of the date, when the date format is '2013-01-01', the result is '01-01', which is fine. When the date format is the abbreviated format of '2013-1-1', the result is '3-1-1', which will cause an error.

Then I thought of a third method based on the date function in the MYSQL help document:

Method 3

SELECT FLOOR(DATEDIFF(CURDATE(), @birthday)/365.2422)

Take the number of days between your birthday and the current date, divide it by the actual number of days in a year (365 days, 5 hours, 48 ​​minutes, and 46 seconds), and then round up. This is done with just three functions and one operator.

Then, I quickly found the fourth method on a foreign website:

Method 4

 SELECT TIMESTAMPDIFF(YEAR, @birthday, CURDATE())

This method only uses two functions to complete the task, which should be the best method.

After testing the above four methods, if the current date is '2017-1-13', when the birthday is '2013-1-14', there is only one day left before the birthday, and only one day away from 4 years old, but the result is still 3 years old, which does not seem reasonable; modify method 3 and round it off to get method 5:

Method 5

SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)

The age calculated in this way is closest to the actual age, but method four may be the most consistent with the age definition.

Summarize

The above is what I introduced to you about calculating age by birthday in MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!