Mysql calculates age based on date of birth

Recently because the business requirements need to calculate the age according to the date of birth, on-line check a lot of methods, summarized here.

On the Internet, many of the calculation methods have referred to the Gregorian calendar, deliberately to check the next information, popularize knowledge.

Gregorian Gregorian calendar is the standard name of the calendar, is a kind of calendar originated from the Western society. It was first made by an Italian physician, astronomer, philosopher, and chronology of the Ainu Sch Rilliouse (Aloysius Lilius, circa 1519-1576)

and Crau (Christophorus Clavius) and other scholars on the basis of the Julian calendar reform, after Pope Gregory 13 was promulgated in 1582. and A.D. is "Gregorian era", also known as "vids". Is the Gregorian calendar we have become.

Its regression year is 365 days, 5 hours, 48 minutes, 45.5 seconds, and the number of days in a year is about 365.2422 天，下面计算年龄时会用到这个数字。


In March 1, 1582, Gregory issued a Change Order: the content is:

The day after October 4, 1582 is October 15, not October 5, but the week ordinal is still calculated consecutively: October 4 is Thursday, the next day October 15 is Friday. In this way, the raking accumulated since the 325 A.D. written off.

Second, in order to avoid the phenomenon of the vernal equinox drifting away, the leap year method is: where the number of years can be divisible by 4 is a leap year, but when the number of years behind is with two "0" of the "century", must be able to be divisible by 400 years is the leap.

MySQL computing age has a good variety of ways, in summary, can be based on their business needs and preferences to choose to use.

Method One:

SELECT Date_format (From_days (To_days (now ())-'%Y')+0as Age

Use To_days () and from_days () two functions to complete the conversion between the date and the number of days, and the last to take the year of the date, +0 at the end of the statement to remove the previous 0 after the date format conversion.

Improved:

SELECT  Year (From_days (DATEDIFF(now (), birthday)))  from Employee_out;

Method Two:

SELECT  Year (Curdate ()) -  Year ('2019-02-02') - (Right (Curdate (),5)<right('2019-02-02'), 5));

This is done to calculate that the current year is less than the year that needs to be calculated, and the result of the query is negative when the current year is less than the year that needs to be calculated.

Method Three:

SELECT  Floor (DATEDIFF' 1993-01-23 ') / 365.2422

Calculates the actual age by dividing the number of days by the number of days in the Gregorian calendar year and rounding down.

Method Four:(recommended)

SELECT Timestampdiff (year,'1993-01-02', Curdate ())

Calculates the number of years between dates, which is age.

　

Mysql calculates age based on date of birth