How old are you???
Everyone knows how old they are, right?? Well OK, so sometimes I have to think about it a couple of times just to check (what with it nearly being my birthday) but still, it’s pretty easy.
However if you’re a database without a function to find out age it’s a tad more tricky.
This just came up as someone in the office was writing a report that included calculating the users ages . . . the solution they used was good enough for our purposes but was also one of those little things that bugs you because you know there’s a better way to do it.
The initial solution was as follows . . . btw, let’s also chuck the date of birth into a variable to simplify the queries 😉
SET @dateOfBirth=’1972-10-03′;
SELECT (DATEDIFF(NOW(), @dateOfBirth))/365;
As I said it’ll work but obviously there are going to be cases when it’s not quite right. The problem is obviously caused by the number of days in a year being inconsistent. Now MySQL has plenty of date functions built into it that we can leverage here so let’s see what we can come up with.
In order to get some more precision I first thought to convert the period from DOB to now into seconds . . .
SELECT datediff(NOW(), @dateOfBirth)*24*60*60;
We can then convert that back to a date since epoch using,
SELECT from_unixtime((datediff(NOW(), @dateOfBirth))*24*60*60);
then we rip the year out of that newly created date and take 1970 away from it to get the final age . . .
SELECT date_format(from_unixtime((datediff(NOW(), @dateOfBirth))*24*60*60), ‘%Y’)-1970;
BINGO . . . err, oopsie . . . that ain’t gonna work for anyone born before the epoch is it (because the from_unixtime function will return NULL for any negative values passed to it). As usual I’ve gone off in the first direction I’d thought of and not bothered th check things out on the way. Deep breath time and start again then . . .
Let’s start simple,
SELECT YEAR(CURRENT_DATE()) – YEAR(@dateOfBirth);
That’ll work fine as long as the month/day of the birthday has already been passed during the current year, if it hasn’t we need to take one away (eugh, sounds hacky again). To find if we’ve passed that date we can use,
SELECT date_format(CURRENT_DATE(), ‘%m-%y’)<date_format(@dateOfBirth, ‘%m-%y’);
and then putting it all together,
SELECT YEAR(CURRENT_DATE()) – YEAR(@dateOfBirth) – (date_format(CURRENT_DATE(), ‘%m-%y’)<date_format(@dateOfBirth, ‘%m-%y’)) AS age;
Well . . . it works but I know there must be a simpler way to do it – I’ll have another look later when I’m not in the office but at least this woodpecker has been put to bed for now!
Comments
Leave a Reply
