mysql 定义年龄属性_MySQL按年月日计算年龄

I want to display data of patients age.

mysql> select nama,gender,dob,TIMESTAMPDIFF(YEAR,dob,now()) as age from sampelaja;

+------------------+--------+------------+------+

| nama | gender | dob | age |

+------------------+--------+------------+------+

| Rizkiyandi | 1 | 2010-05-21 | 4 |

| Siti Khodijah | 0 | 1980-03-15 | 34 |

| Aisyah Az-zahra | 0 | 1986-08-17 | 28 |

| Paritem | 0 | 2005-12-13 | 8 |

| Ngadimin | 1 | 2014-08-28 | 0 |

+------------------+--------+------------+------+

10 rows in set (0.00 sec)

Here there is a problem when there is a 4-day-old baby who is regarded as the age of 0 year

I want a result like this

+------------------+--------+------------+------+-------+------+

| nama | gender | dob | year | month | day |

+------------------+--------+------------+------+-------+------+

| Rizkiyandi | 1 | 2010-05-21 | 4 | 3 | 13 |

| Siti Khodijah | 0 | 1980-03-15 | 34 | 5 | 18 |

| Aisyah Az-zahra | 0 | 1986-08-17 | 28 | 0 | 16 |

| Paritem | 0 | 2005-12-13 | 8 | 8 | 20 |

| Ngadimin | 1 | 2014-08-28 | 0 | 0 | 6 |

+------------------+--------+------------+------+-------+------+

解决方案

You can use modulo to determine count of months and days:

SELECT

nama

, gender

, dob

, TIMESTAMPDIFF( YEAR, dob, now() ) as _year

, TIMESTAMPDIFF( MONTH, dob, now() ) % 12 as _month

, FLOOR( TIMESTAMPDIFF( DAY, dob, now() ) % 30.4375 ) as _day

FROM

sampelaja

The result is:

+-----------------+--------+------------+-------+--------+------+

| nama | gender | dob | _year | _month | _day |

+-----------------+--------+------------+-------+--------+------+

| Rizkiyandi | 1 | 2010-05-21 | 4 | 3 | 13 |

| Siti Khodijah | 0 | 1980-03-15 | 34 | 5 | 19 |

| Aisyah Az-zahra | 0 | 1986-08-17 | 28 | 0 | 17 |

| Paritem | 0 | 2005-12-13 | 8 | 8 | 20 |

| Ngadimin | 1 | 2014-08-28 | 0 | 0 | 6 |

+-----------------+--------+------------+-------+--------+------+

Days are calculated between birthday date from previous month till today.

Number 30.4375 I calculated using this formula: [DAYS IN YEAR]/12, where [DAYS IN YEAR] = 365.25