我想显示患者年龄的数据。
mysql>从sampelaja中选择nama、gender、dob、TIMESTAMPDIFF(YEAR、dob,now())作为年龄;+------------------+--------+------------+------+|nama|性别|dob|年龄|+------------------+--------+------------+------+|Rizkiyandi |1 | 2010年5月21日| 4||Siti Khodijah | 0 | 1980-03-15 | 34||Aisyah Az zahra | 0 | 1986-08-17 | 28||奇偶校验|0|2005-12-13|8||Ngadimin |1 | 2014-08-28 |0|+------------------+--------+------------+------+一组10行(0.00秒)
这里有一个问题,当有一个4天大的婴儿被认为是0岁的时候我想要这样的结果
+------------------+--------+------------+------+-------+------+|nama|gend|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||奇偶校验|0|2005-12-13|8|8|20||Ngadimin |1|2014-08-28|0|0|6|+------------------+--------+------------+------+-------+------+
您可以使用模来确定月份和天数:
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
:[DAYS IN YEAR]/12,其中[DAYS IN YEAR]=365.25
您应该能够使用下面的查询来计算此值。查询计算准确的年、月和日。
这些信息也可以在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
接受答案中的问题
公认答案中的方法是好的,但闰年在生日时会把它搞砸。以下是一些测试用例:
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
Jaugar Chang的答案中的days列在日期相同时出现故障。我认为以下纠正了这一点:
(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
这是一个我用来计算年龄的查询,以年、月和天为单位
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
以下函数从各种资源中复制并合并为一个。这将返回以年、月和天为单位的完整日期。它也可以被修改为显示任何一个。此外,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 ;
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
您可以使用
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
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;