MySQL按年月日计算年龄 [英] MySQL Calculate age by year month and day

问题描述

我想显示患者年龄的数据.

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)

这里有一个问题，当有一个4天大的婴儿被视为0岁 我想要这样的结果

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

结果是:

+-----------------+--------+------------+-------+--------+------+
| 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.

数字30.4375我使用以下公式计算得出:[年中的天数]/12，其中[年中的天数] = 365.25

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

这篇关于MySQL按年月日计算年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！