How to Store Birthdate and Age So That Age Can Be Updated Daily in PHP/Mysql

How to store Birthdate and Age so that Age can be updated daily in PHP/MySQL?

The simple answer is don't; never store a persons age. It changes for each person yearly but, as you say, you have to check that it's correct for every person daily.

Only store the date of birth, and then calculate the age when selecting from the database. It's only today - date of birth so takes almost no CPUs at all.

EDIT:

To expand upon my comment in ManseUK's answer there's also the possibility of failure. What happens if your server / database is down? Or your update fails to run at its specified time? Or someone comes along and runs it manually after the update already been run for that date? Or someone turns off your scheduler? There's no danger of this happening if you calculate Age as you select from the database.

To select where age is between 25 and 30 years and assuming a DATE column dateofbirth your query would be something like:

select *
  from users
 where dateofbirth between date_add( curdate(), interval -30 year )
                       and date_add( curdate(), interval -25 year )

Ensure users is indexed on dateofbirth.

How to convert a date of birth into an age and save it into a table?

You can do this directly in SQL,

Try something like

INSERT INTO Member_Detail (id, dob)
VALUES SELECT id, TIMESTAMPDIFF(YEAR, dob, CURDATE()) FROM Member

SQL age update when it = current date

EDIT:

This is the query you need to update age for users who has dob on current day.

UPDATE table_a a
SET a.age = TIMESTAMPDIFF(YEAR, dob, CURDATE())
WHERE a.id IN (
      SELECT id
      FROM (SELECT * FROM table_a)  AS temp_table
      WHERE DATE_FORMAT(dob,'%m-%d') = DATE_FORMAT(CURDATE(),'%m-%d')
)

SQLFIDDLE.

You can't use DAYOFYEAR as it considers leap years i.e. this method will return 212 for 2015-07-31 but will return 213 for 2012-07-31.

Secondly, to use the same table in update query, it should be used as temporary table. Explanation here.

---

Reason for SQL Syntax error is that you're missing FROM clause in inner query. i.e. SELECT timestampdiff(year, dob, curdate()) AS age WHERE .... Add FROM table_a.

If birthdate's are stored as type Date in MySQL, how would you determine if it's a user's birthday today in PHP?

Since you'll need to exclude the year, you can use the MONTH and DAY SQL functions like so:

SELECT * FROM table WHERE MONTH(birthday) = MONTH(NOW()) AND DAY(birthday) = DAY(NOW());

Don't forget to add an index on this column or it will seriously degrade performance as data grows.

In PHP, you can just use the date function:

if (date('m-d', strtotime($row['date'])) == date('m-d'))
    //-- Today is my birthday! Hooray!

Changing a field in MYSQL automatically

Use the mysql scheduler (so long as your mysql version is 5.1.6+).

CREATE EVENT birthdayEvent
  ON SCHEDULE
    EVERY 1 DAY
    STARTS '2015-05-01 04:00:00' ON COMPLETION PRESERVE ENABLE 
  DO
    update user set user_level = 2 where user_birthday = date(now() - interval 13 year);

Make sure the scheduler is enabled with set global event_scheduler = on. Then this event will run every day, at 4am, and update the user levels for everyone whose birthday fell 13 years ago.

store calculated data or pre-calculated data on table (calculated user age or timestamp)

It mostly depends on the purpose of the use of the column. If you never need to do any time to time calculations (addition, subtraction etc) based on when the like occurred, than the age of the person is completely valid (basically you're pre-evaluating birthDate-likeDate). However, if you have any intention of saying that someone liked a game 10 days ago, it will be much more work to reverse the pre-evaluated user-age back to a likeDate to allow another calucation. Keeping in mind that you most likely want to keep your database data extensible, using likedAt is preferred in my opinion.

I would also note that stackoverflow is not a site designed to answer theoretical questions that don't have a specific code answer, this question should probably be on programmers.stackexchange.com.

How to find users aged between 25 and 30 when the birthdates are stored as type date in MySQL?

You just need to switch the two dates add another day to the first date, like this:

WHERE birth_date 
BETWEEN 
    date_add(
        date_sub(curdate(), INTERVAL 31 YEAR), 
        INTERVAL 1 DAY
    )
AND date_sub(curdate(), INTERVAL 25 YEAR) 

Looking at the other proposed answer, actually there is a simpler solution.

WHERE birth_date <= date_sub(curdate(), INTERVAL 25 YEAR) 
      AND birth_date > date_sub(curdate(), INTERVAL 31 YEAR)

Note the > on the second comparison, that saves adding the 1 DAY interval, but you still need to use 31 YEARs, not 30. The BETWEEN AND expression will do >= and <=.

(Please accept his answer when it proves to be correct and he/you edited the comparison and changed the 30 to 31- he pointed me in the right direction)

how to calculate age from date of brith and store it in the database

You can add or subtract dates with JavaScript - if they are formatted as dates:

const birthDate = new Date('1980.02.18')const today = new Date(Date.now())
console.log('birth date:', birthDate)console.log('today:', today)
console.log('age in years:', new Date(birthDate - today).getYear())

Calculate and create categories age display sum by column

you can use a cross join with the column name to transpose it

SELECT 
    age_range,
    CASE age_range 
        WHEN '<18' THEN SUM(CASE WHEN AGE <18 THEN 1 ELSE 0 END)
        WHEN '18-24' THEN SUM(CASE WHEN AGE >= 18 AND AGE <= 24 THEN 1 ELSE 0 END)
        WHEN '25-34' THEN SUM(CASE WHEN AGE >= 25 AND AGE <= 34 THEN 1 ELSE 0 END)
        WHEN '35-44' THEN SUM(CASE WHEN AGE >= 35 AND AGE <= 44 THEN 1 ELSE 0 END)
        WHEN '45-54' THEN SUM(CASE WHEN AGE >= 45 AND AGE <= 54 THEN 1 ELSE 0 END)
        WHEN '>=55' THEN SUM(CASE WHEN AGE >= 55 THEN 1 ELSE 0 END)
    END value
FROM 
(   SELECT 
        t_personne.pers_date_naissance, 
        t_personne.pers_date_inscription, 
        TIMESTAMPDIFF(Year, t_personne.pers_date_naissance, t_personne.pers_date_inscription) 
        - CASE 
            WHEN MONTH(t_personne.pers_date_naissance) > MONTH(t_personne.pers_date_inscription) 
             OR (MONTH(t_personne.pers_date_naissance) = MONTH(t_personne.pers_date_inscription) 
             AND DAY(t_personne.pers_date_naissance) > DAY(t_personne.pers_date_inscription))
            THEN 1 ELSE 0 
          END AS AGE
    FROM t_personne
) AS Total
CROSS JOIN
(   SELECT '<18' age_range UNION ALL
    SELECT '18-24' UNION ALL
    SELECT '25-34' UNION ALL
    SELECT '35-44' UNION ALL
    SELECT '45-54' UNION ALL
    SELECT '>=55'
)a
GROUP BY age_range
ORDER BY FIELD(age_range, '<18', '18-24', '25-34', '35-44', '45-54', '>=55')

FIDDLE DEMO

Calculate the date difference everyday automatically

You don't. You store the date it was created in the database. Then, when you need to use it, compute the value (dateDiff) upon retrieval. It is not the job of the database to compute values and update itself daily and your current approach has this set as an expectation.

---

Related Topics