CODEWITHSUNDEEP
phpmysqlsql
user2710234
user2710234

Reputation: 3225

PHP work out age in years and months from timestamp

I am running this SQL:

SELECT TIMESTAMPDIFF(YEAR,CONCAT(dob_year, '-', dob_month, '-', dob_day),CURDATE()) as age FROM members

to work out users ages from a timestamp in the database.

I have 3 columns (dob_year / dob_month / dob_day)

the above query works out the age in years but I need it in years and months. So rather than age being 10 years, it should say 10 years and 2 months old.

Upvotes: 3

Views: 189

Answers (2)

Kasper Sanguesa-Franz
Kasper Sanguesa-Franz

Reputation: 617

this should work giving you 2 columns

1. years
2. months


SELECT 
TIMESTAMPDIFF(YEAR,CONCAT(dob_year, '-', dob_month, '-', dob_day),CURDATE()) 
as years,
TIMESTAMPDIFF(MONTH, 
    TIMESTAMPADD(YEAR,
        TIMESTAMPDIFF(YEAR,CONCAT(dob_year, '-', dob_month, '-', dob_day),CURDATE())
    ,CONCAT(dob_year, '-', dob_month, '-', dob_day)),CURDATE()) 
as 
   months 
FROM 
   members

reference

Upvotes: 0

number50
number50

Reputation: 153

    SELECT 
       TIMESTAMPDIFF(YEAR,CONCAT(dob_year, '-', dob_month, '-', dob_day),CURDATE()) 
      as 
        years, 
        (TIMESTAMPDIFF(MONTH,CONCAT(dob_year, '-', dob_month, '-', dob_day),CURDATE()) - 
        TIMESTAMPDIFF(YEAR,CONCAT(dob_year, '-', dob_month, '-', dob_day),CURDATE())*12) 
      as 
        months 
      FROM 
        members

Upvotes: 3

Related Questions