I just got an email from
firstname.lastname@example.org saying the following:
The user comment system in the MySQL manual is not the place to request features. You can do so using our bug-tracking system at http://bugs.mysql.com/. Thanks. (Actually, your comment is not a feature request, but it relates to another comment that is. The example you’re giving is nice, but this is a reference manual, so we have to restrict it to _a few_ useful examples.)
My original comment was:
You bring up some important issues, but dealing with ages really isn’t that hard. For example you could do something like this:
mysql> SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0 AS age FROM people;
Where ‘dob’ is obviously their date of birth. It’ll also work with pre and post-epoch dates. Please excuse the funky formatting as the
comment stem seems to insist on inserting line breaks into the code block. I ran into this problem while working on some genealogical things over at Mullenweg.com, a family site. I hope this helps!
Looking back, it’s funny that the comment is still around, I wrote it over two years ago. The date and time functions is the MySQL page I use most, so in some sense it was always nice to have my mark on there. For google and posterity I’ve preserved the comment here.
I’m glad they’re cleaning up the comments, as they are really bad in places and have atrocious formatting, especially when compared to say, the PHP manual. However there is a later comment (which is still up) that offers perhaps a better method. From Kirill Novitchenko:
The method posted by Mathew Mullenweg is good, but leap years goof it up on birthdays. (Try it. Use the current date and subtract exactly 5 years ago.)
Hopefully this will be the last ‘find age’ function. There is a simple premise to it:
- Subtract the current year from the birth year to get the age.
- If the current month and date is less than the birth month and date, subtract 1 from step 1.
Therefore, this should work with everyone who wasn’t born in the future.
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
where dob is date of birth.
I’ve never run into any problems with my function but I see nothing wrong with the way this one works, so I may update my code to use it.
Why not just use unix timestamps and avoid all the funkiness? When I first started writing everything I actually did, but then one day I got a call from my lovely sister saying that it was showing everyone’s birthday as January 8th, 1901 (or something like that). I had reached the negative limit of a 32-bit integer, the upper limit being sometime in 2038. Moving all the date functions into the SQL is probably bad from a programming point of view but it works great for the application. Of course I have no clue how it deals with the 10 days Pope Gregory removed from the calendar in 1582. Hopefully that won’t come up. 🙂