If DOB column is encrypted, how would I go about calculating the age of each person in MySQL

Question

I was given a database where the DOB column is encrypted with crypto. Assuming that the column has to stay encrypted, how would I go about getting/updating the age for each user?

Currently, I calculate the age using the code below : (userBirthday is given in YYMMDD format and I calcualte the age before encrypting DOB)

function get_age(time) {
    var year = time.slice(0,4);
    var month = time.slice(4,6);
    var day = time.slice(6);
    //month is 0-indexed
    var years_elapsed = (new Date(new Date().getTime() - (new Date().getTimezoneOffset() * 60000)) - new Date(year, month - 1, day))/(MILLISECONDS_IN_A_YEAR);
    return Math.floor(years_elapsed); 
}

let birthdayFull = req.body.userBirthday;
let birthYr = birthdayFull.slice(0,2);
let currentYear = new Date().getFullYear().toString().slice(2);

if(birthYr < currentYear) {
    birthdayFull = '20' + birthdayFull;
} else {
    birthdayFull = '19' + birthdayFull;
}
let age = get_age(birthdayFull);

Depending on the year, I either add '19' or '20' to get the date into a YYYYMMDD format. However, if I run the get_age function, I get an inaccurate answer. For example, if DOB is '19920215', the return value is 29, even though the user is still 28.

My question is 1. How would I calculate the user age accurately (getting the actual age, aka adding a year only if the actual birthday has passed) and 2. if I want to check for age updates everyday at midnight (to check if someone's age changed), how would I implement it if the DOB column is encrypted?

Answer 1

Answers to your two questions:

1. Subtract DoB from the current date, convert to years and round down
2. Holding data values that change based on a user's perspective (i.e. depending on what day they look at it) in a database is a really bad practice. You should calculate this dynamically when the user queries the data

Update Following Comment

1. Don't encrypt the DoB in the table. Remove user access from the table if you don't want people to be able to see the DoB

2. Create a View over the table that excludes or masks the DoB column. Add calculated columns to the view that display the age-related information that you want