Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
Store a person’s date of birth in a DATE column, record form-submission time in a DATETIME column, and calculate current age when you need to show it. Don’t normally save someone’s changing current age in an age column: it becomes stale after a birthday.
Use a birth date as the source of truth
A date of birth is a stable fact; current age is derived from that fact and the date you ask. For example, store 1976-01-12, then calculate how many complete years have passed. A stored age such as 42 would need regular updates and could disagree with the birth date after a birthday or correction.
There are legitimate exceptions: a system may need to preserve someone’s age at a specific historical event. In that case, store a clearly named snapshot such as age_at_consent along with the event date. That is different from storing current age.
Recommended MySQL table
CREATE TABLE people (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birth_date DATE NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);Use DATE for a birthday because the time of day is not relevant. MySQL represents date values in YYYY-MM-DD form; see the MySQL date and time type reference. Avoid storing dates as VARCHAR or as an encoded integer: text formats can be ambiguous, and typed dates make comparison, sorting, and date arithmetic more dependable.
#1 Best Overall
If a date of birth is optional, define birth_date DATE NULL and decide how the application will handle a missing value. Don’t insert a guessed date just to make the column non-null.
Record when the form was submitted
The created_at column records the date and time the row was created. Because it has DEFAULT CURRENT_TIMESTAMP, MySQL supplies a value when a row is inserted without one:
INSERT INTO people (name, birth_date)
VALUES ('Example Person', '1976-01-12');MySQL documents automatic timestamp initialization for DATETIME and TIMESTAMP columns in its timestamp initialization reference. This example uses DATETIME; choose TIMESTAMP only when its timezone-conversion behavior and range fit your application’s conventions.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchDo not add ON UPDATE CURRENT_TIMESTAMP to a creation-time column unless you want the original submission time to change every time the row is edited. If you also need to track edits, use a separate field:
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMPUse DATE instead if you truly need only the calendar day, not the time. A birth date, a submission time, and a last-updated time describe different things and should not share one column.
Calculate age in a query
Use MySQL’s TIMESTAMPDIFF to calculate completed years:
Rank #3
SELECT
id,
name,
birth_date,
created_at,
TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM people;MySQL documents this pattern for calculating age in its date-calculations guide. The result includes an age field in the query output even though no age column is stored in the table. Each time the query runs, the calculation uses the current date; it does not rewrite the row on a birthday.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →For one record, add a condition such as WHERE id = 123. In phpMyAdmin, the table view shows stored columns such as birth_date and created_at. To see the derived age there, run a query that selects the expression with the AS age alias.
Filter by age
This is readable and works for many uses:
SELECT *
FROM people
WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) >= 18;For a large table, applying a function to every birth_date in a filter can make ordinary index use less effective. A date boundary is often preferable for the same “18 or older” rule:
SELECT *
FROM people
WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 18 YEAR);Choose one consistent interpretation of the cutoff for your application. Neither expression by itself defines legal adulthood in every jurisdiction or resolves special rules for leap-day birthdays.
Calculate in PHP if that suits the application
You can also retrieve the stored date and calculate age in the application layer:
$birthDate = new DateTimeImmutable($row['birth_date']);
$today = new DateTimeImmutable('today');
$age = $birthDate->diff($today)->y;SQL is convenient for reports and age-based filtering; PHP is useful when the application owns display logic. The key design rule is the same: store the birth date once and derive current age consistently where it is needed.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Accept and validate date input
A browser form can use a date input:
<label for="birth_date">Date of birth</label>
<input type="date" id="birth_date" name="birth_date" required>The browser’s date-picker appearance and visible format can vary by browser and locale. The submitted value is intended to be machine-readable, and your server should normalize and validate it before inserting it as a MySQL date. Keep three concerns distinct: the user-facing display format may be localized, the stored value should be unambiguous such as 1976-01-12, and server-side validation must verify that the submitted date is real.
For PHP input expected in ISO form, a strict check can reject impossible dates and unexpected formats:
$input = $_POST['birth_date'] ?? '';
$birthDate = DateTimeImmutable::createFromFormat('!Y-m-d', $input);
$errors = DateTimeImmutable::getLastErrors();
if (
!$birthDate ||
($errors !== false && ($errors['warning_count'] || $errors['error_count'])) ||
$birthDate->format('Y-m-d') !== $input
) {
throw new InvalidArgumentException('Invalid birth date.');
}
if ($birthDate > new DateTimeImmutable('today')) {
throw new InvalidArgumentException('Birth date cannot be in the future.');
}HTML validation alone is not enough: clients can submit requests without using the form, and browser behavior varies. If you must support a text field as a fallback, explicitly parse the format you told the user to enter, convert it to ISO form, and reject impossible dates rather than silently changing them.
Edge cases to plan for
- February 29: Store the real date, such as
2000-02-29.TIMESTAMPDIFFgives the ordinary completed-years calculation; if a legal or business policy treats a non-leap-year birthday as February 28 or March 1, implement that policy explicitly. - Only a year or month is known: Don’t invent a day. Use a nullable date plus an explicit precision indicator, or model known components separately. Don’t present an exact age as certain when the exact birth date is unknown.
- Future dates: Reject them for birth dates unless there is a defined reason to accept them. Validate on the server; do not rely only on a browser’s date-input limits.
- Historical age: If you need age at registration or another event, preserve that snapshot and the date it refers to. Don’t label it simply
ageif readers might mistake it for current age. - Date versus moment: A birthday is a calendar date, not a timestamp. Using a time-bearing type unnecessarily can create timezone-related confusion about which calendar day is being shown.
Why not use age INT(3)?
The number in parentheses in legacy MySQL integer declarations such as INT(3) was a display-width convention, not a maximum of three digits. More importantly, choosing a different integer type does not solve the main problem: current age changes, while the birth date is the source data from which it can be calculated.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
