Calculate age in months and years OR format seconds as years and months?

Question

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.

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.

How can I do that?

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

Answer 1

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

Answer 2

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?