计算几个月和几年的年龄或将秒数格式化为年和月？

I need to find out the age of a person at a certain point in time. I've got both the DOB and this 'point in time' as unix timestamps.

我需要在某个时间点找出一个人的年龄。我有DOB和这个'时间点'作为unix时间戳。

I could subtract them to get their age in seconds at that time, but... I don't see any MySQL functions to format it into years and months.

我可以在那时减去它们以在几秒钟内获得它们的年龄，但是......我没有看到任何MySQL函数将其格式化为数年和数月。

How can I do that?

我怎样才能做到这一点？

Specifically, the format I want is 5y 6m.

具体来说，我想要的格式是5y 6m。

2 个解决方案

#1

This is your query:

这是您的查询：

SELECT CONCAT(
    TIMESTAMPDIFF(YEAR, FROM_UNIXTIME(`dob`), FROM_UNIXTIME(`point_in_time`)), 
    'y ',
    MOD(TIMESTAMPDIFF(MONTH, FROM_UNIXTIME(`dob`), FROM_UNIXTIME(`point_in_time`)), 12),
    'm'
) `age`
...
...

Another one might be:

另一个可能是：

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

Hope this helps?

希望这可以帮助？

#2

SELECT CONCAT(    
  YEAR(DATE_ADD('2000-01-01',INTERVAL (DATEDIFF(FROM_UNIXTIME(tpit), FROM_UNIXTIME(dob))) DAY))-2000, 'y ',
  MONTH(DATE_ADD('2000-01-01',INTERVAL (DATEDIFF(FROM_UNIXTIME(tpit), FROM_UNIXTIME(dob))) DAY)), 'm'
  ) as output 
FROM ..... WHERE .....

#1