php MySQL按年月日计算年龄

daupos2t  于 2023-03-28  发布在  PHP
关注(0)|答案(9)|浏览(138)

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

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岁的年龄我想要这样的结果

+------------------+--------+------------+------+-------+------+
| 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 |
+------------------+--------+------------+------+-------+------+
91zkwejq

91zkwejq1#

您可以使用modulo来确定月和天的计数:

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

天数是从上个月的生日到今天计算的。
使用以下公式计算的数量30.4375 I:[年天数]/12,其中[年天数] = 365.25

bnl4lu3b

bnl4lu3b2#

您应该能够使用下面的查询计算此值。该查询计算精确的年、月和日。
这些信息也可以在mysql日期计算页面找到:http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

SELECT 
    nama, 
    gender, 
    dob,

    /* Select the number of years */
    TIMESTAMPDIFF(
        YEAR,
        dob,
        CURDATE()
    ) AS years,

    /* Select the number of months by adding the number of years to the 'dob' date field */
    TIMESTAMPDIFF(
        MONTH, 
        DATE_ADD(
            dob ,
            INTERVAL TIMESTAMPDIFF(YEAR,dob,CURDATE()) YEAR
        ),
        CURDATE()
    ) AS months,

    /* Select the number of days by adding the number of years and number of months to the 'dob' field */
    TIMESTAMPDIFF(
        DAY,
        DATE_ADD(
            DATE_ADD(
                dob ,
                INTERVAL TIMESTAMPDIFF(YEAR,dob,CURDATE()
            ) YEAR),
            INTERVAL TIMESTAMPDIFF(
                MONTH, 
                DATE_ADD(
                    dob ,
                    INTERVAL TIMESTAMPDIFF(YEAR,dob,CURDATE()) YEAR
                ),
                CURDATE()
            ) MONTH
        ),
        CURDATE()
    ) AS days
FROM
    sampelaja
jjjwad0x

jjjwad0x3#

接受答案存在问题

接受答案中的方法是好的,但闰年在生日时会出错。下面是一些测试案例:

create table sample (dob datetime,now datetime);
insert into sample (dob,now)values
('2012-02-29', '2013-02-28'),
('2012-02-29', '2016-02-28'),
('2012-02-29', '2016-03-31'),
('2012-01-30', '2016-02-29'),
('2012-01-30', '2016-03-01'),
('2011-12-30', '2016-02-29');

SELECT
      date_format(dob,'%Y-%m-%d')
    , date_format(now,'%Y-%m-%d')
    , TIMESTAMPDIFF( YEAR, dob, now ) as _year
    , TIMESTAMPDIFF( MONTH, dob, now ) % 12 as _month
    , FLOOR( TIMESTAMPDIFF( DAY, dob, now ) % 30.4375 ) as _day
 FROM sample

       DOB         NOW YEAR     MONTH  DAY
2012-02-29  2013-02-28    0     11      30  -- 28 days would be better
2012-02-29  2016-02-28    3     11      29  -- 28 days would be better
2012-02-29  2016-03-31    4      1       0  -- 2 days would be better
2012-01-30  2016-02-29    4      0      30  
2012-01-30  2016-03-01    4      1       0  -- 2 days should be right
2011-12-30  2016-02-29    4      1       0  -- The right answer should be 4 years 1 months 30 days

我的方法是,只使用算术

select
       DATE_FORMAT(dob,'%Y-%m-%d'),
       DATE_FORMAT(now,'%Y-%m-%d'),
       FLOOR(( DATE_FORMAT(now,'%Y%m%d') - DATE_FORMAT(dob,'%Y%m%d'))/10000),
       FLOOR((1200 + DATE_FORMAT(now,'%m%d') - DATE_FORMAT(dob,'%m%d'))/100) %12,
      (sign(day(now) - day(dob))+1)/2 * (day(now) - day(dob)) + 
      (sign(day(dob) - day(now))+1)/2 * (DAY(STR_TO_DATE(DATE_FORMAT(dob + INTERVAL 1 MONTH,'%Y-%m-01'),'%Y-%m-%d') - INTERVAL 1 DAY)
                                         - day(dob) + day(now))
       -- Explain: if the days of now is bigger than the days of birth, then diff the two days
       --          else add the days of now and the distance from the date of birth to the end of the birth month 
from sample

测试用例及结果:

DOB         NOW  YEARS   MONTHS  DAYS
2012-02-29  2013-02-28     0    11      28
2012-02-29  2016-02-28     3    11      28
2012-02-29  2016-03-31     4     1       2
2012-01-30  2016-02-29     4     0      30
2012-01-30  2016-03-01     4     1       2
2011-12-30  2016-02-29     4     1      30
9bfwbjaz

9bfwbjaz4#

当日期是同一天的时候,Jaugar Chang的答案中的日期栏有一个小问题。我认为下面的内容可以纠正这个问题:

(SELECT CASE sign(day(now)-day(dob)) 
  WHEN 0 THEN 0 
  WHEN 1 THEN day(now)-day(dob)
  ELSE (DAY(STR_TO_DATE(DATE_FORMAT(dob + INTERVAL 1 MONTH,'%Y-%m-01'),'%Y-%m-%d')-INTERVAL 1 DAY)-day(dob)+day(now)) END)
  as days

为了完整起见,Jaugar的回答的修正版本如下:

SELECT 
FLOOR(( DATE_FORMAT(NOW(),'%Y%m%d') - DATE_FORMAT(dob,'%Y%m%d'))/10000) as year,
FLOOR((1200 + DATE_FORMAT(NOW(),'%m%d')-DATE_FORMAT(dob,'%m%d'))/100) %12 as month,
CASE sign(day(NOW())-day(dob))
  WHEN 0 THEN 0
  WHEN 1 THEN day(NOW())-day(dob)
  ELSE (DAY(STR_TO_DATE(DATE_FORMAT(dob + INTERVAL 1 MONTH,'%Y-%m-01'),'%Y-%m-%d')-INTERVAL 1 DAY)-day(dob)+day(NOW())) END as day
FROM sampelaja
bybem2ql

bybem2ql5#

这是一个我用来计算年龄的查询,以年、月、日为单位

SELECT nama, gender, dob
,DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS years
,PERIOD_DIFF( DATE_FORMAT(CURDATE(), '%Y%m') , DATE_FORMAT(dob, '%Y%m') ) AS months
,DATEDIFF(CURDATE(),dob) AS days
FROM sampelaja
qlckcl4x

qlckcl4x6#

下面的函数是从各种资源中复制并合并成一个。这将返回完整的日期,以年,月和日表示。也可以修改它以显示任何一个。还可以将current_time作为输入,以便我们也可以计算相对于任何其他日期的年龄。

DROP function IF EXISTS `calculate_age`;

DELIMITER $$
CREATE FUNCTION `calculate_age`(`dob` DATE,  `current_time` DATETIME) 
RETURNS varchar(100) CHARSET utf8
BEGIN
DECLARE years varchar(10);
DECLARE months varchar(9);
DECLARE days varchar(7);
SELECT FLOOR(DATEDIFF(current_time, dob)/365) INTO years;
SELECT FLOOR((DATEDIFF(current_time,dob)/365 - FLOOR(DATEDIFF(current_time,dob)/365))* 12) INTO months;
SELECT CEILING((((DATEDIFF(CURDATE(),dob)/365 
- FLOOR(DATEDIFF(CURDATE(),dob)/365))* 12) - FLOOR((DATEDIFF(CURDATE(),dob)/365 - FLOOR(DATEDIFF(CURDATE(),dob)/365))* 12))* 30) into days;
RETURN CONCAT_WS
            ( ', '
        , CASE WHEN years = 0 THEN NULL ELSE CONCAT(years,'y') END
        , CASE WHEN months = 0 THEN NULL ELSE CONCAT(months, 'm') END
        , CASE WHEN days = 0 THEN NULL ELSE CONCAT(days, 'days') END
        );
END$$

DELIMITER ;
t3psigkw

t3psigkw7#

SELECT concat(
  cast(TIMESTAMPDIFF(YEAR, str_to_date('14/08/2018','%d/%m/%Y'), str_to_date('14/05/2019','%d/%m/%Y')) AS char),' years ',
    cast(MOD(TIMESTAMPDIFF(MONTH, str_to_date('14/08/2018','%d/%m/%Y'), str_to_date('14/05/2019','%d/%m/%Y')), 12) as char), ' months ',
  cast(DATEDIFF(str_to_date('14/05/2019','%d/%m/%Y'),
  DATE_ADD(DATE_ADD(str_to_date('14/08/2018','%d/%m/%Y'), INTERVAL
  TIMESTAMPDIFF(YEAR, str_to_date('14/08/2018','%d/%m/%Y'),str_to_date('14/05/2019','%d/%m/%Y'))
  YEAR),
  INTERVAL MOD(TIMESTAMPDIFF(MONTH, str_to_date('14/08/2018','%d/%m/%Y'),str_to_date('14/05/2019','%d/%m/%Y')),12) MONTH)) AS char),' days') as Age
c90pui9n

c90pui9n8#

你可以用

SELECT
      nama
    , gender
    , dob
    , TIMESTAMPDIFF( YEAR, dob, now() ) as _year
    , TIMESTAMPDIFF( MONTH, dob, now() ) % 12 as _month
    , TIMESTAMPDIFF( DAY, TIMESTAMPADD(MONTH, TIMESTAMPDIFF(MONTH, dob, now()), dob), now()) as _day
FROM 
    sampelaja

当你除以30.4375精确的日子,你可能不会得到正确的答案。所以你可以计算精确的月份到dob,并从今天的日期减去该日期得到确切的日子。你可以检查/验证相同使用在线年龄计算器在这里https://www.calculator.net/age-calculator.html

l0oc07j2

l0oc07j29#

SELECT TIMESTAMPDIFF(year, dt.dt, NOW()) AS y,
       TIMESTAMPDIFF(month, dt.dt, NOW())%12  AS m,
       TIMESTAMPDIFF ( day, 
        DATE_ADD( adddate(curdate(), day( dt.dt) - day(curdate())), interval 
        -(day( dt.dt)>day(curdate())) month), 
        curdate()) as days
FROM (select date('1975-08-07') as dt ) as dt;

相关问题