MySQL查询获取年龄25/04个月格式，日期格式是1988-04-04? [英] mysql query to get age 25/04 months format where date format is 1988-04-04?

问题描述

我在1988-04-04格式的mysql中有date字段.我需要以25/04个月的格式计算年龄.我尝试过:

I have date field in mysql in 1988-04-04 format.I need to calculate age in 25/04 months format.I tried this:

SELECT CONCAT(
    FLOOR((curdate() - dob) / 31536000),
    'y ',
    FLOOR(MOD((curdate() - dob) / 31536000 * 12, 12)),
    'm'
) `age` from age

这给了我0/0个月的时间.我将不胜感激.

It is giving me 0/0 months. I'll be grateful for any help.

推荐答案

在数字上下文中选择curdate()时，将获得yyyymmdd值，例如20130812(对于今天，2013年8月12日) .这对于日期计算而言并没有那么有用.

When you select curdate() in a numeric context, you get the yyyymmdd value, such as 20130812 (for today, August 12, 2013). That's not really that useful for date calculations.

通过示例，我的生日(我是老屁)将是19650202.计算出今天与今天之间的数值差时，您会得到480610.

By way of example, my birthdate (old fart that I am) would be 19650202. When you work out the numeric difference between that and today, you get 480610.

现在，如果将那个除以31536000(不确定从何处获得)，即使我是48岁的老兄，您肯定会得到零:- )

Now, if you divide that by 31536000 (not sure where you got that from), you definitiely get zero, despite the fact I'm a 48-year-old geezer :-)

使用datediff()计算两个日期之间的天数差，然后应用正确的除法和取模运算，从中获取整年数和月数，您会好得多，例如(未经测试，但应一个良好的开端):

You would be far better off using datediff() to work out the number of days difference between two dates and then applying the correct divide and modulo operations to get full-years and months from that, something like (untested but should be a good start):

select
    floor (100 * datediff (curdate(), dob) / 36525) as years,
    floor (mod (100 * datediff (curdate(), dob), 36525) / 100 / 30) as months
from age

那不会是完美的，因为of年的位置会对其产生一点影响，从长远来看，每年的实际天是365.2425，并且我们假设每个月精确地为30天，但应该在几天之内准确无误.

That won't be perfect since the location of leap-years will affect it a little, the actual days per year is 365.2425 over the long term, and we're assuming exactly 30 days per month but it should be accurate to within a couple of days.

如果想要更精确的度量，则需要找到或实现一些更精确的方程式以计算出值.

If you want a more accurate measure, you need to find or implement some more exact equations for working out the values.

这可能需要使用year()和month()从dob和当前日期中提取相关字段，然后减去这些字段，并调整当前日期是否在当前年份的生日之前.

That's probably going to entail using year() and month() to extract the relevant fields from both dob and the current day and subtracting those, adjusting if if the current date comes before the birthday in the current year.

这篇关于MySQL查询获取年龄25/04个月格式，日期格式是1988-04-04?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！