Geek Answers Handbook

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

I need to know the age of a person at a certain point in time. I have a DOB and this "point in time" as unix timestamps.

I could subtract them to get their age in seconds at that time, but ... I do not see any MySQL functions for formatting this in years and months.

How can i do this?

In particular, the format I need is - 5y 6m.

+5
source share
2 answers

This is your request:

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 may 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
source 
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 .....
+3
source

More articles:


All Articles