A simple way to make birthday queries easier and faster
It’s New Year’s Eve, a date that should strike terror into the hearts of many, because tomorrow a bunch of their queries are going to fail.
Queries to “find all birthdays in the next week” and similar are always a nightmare to write. If you want to see a bunch of examples, go look at the user-contributed comments on the MySQL date and time function reference. This post is about a slightly saner way to do that. There’s still some nasty math involved, but a) a lot less of it, and b) at least the query will be able to use indexes[1].
So here’s my tip: instead of storing the user’s full birthdate, just store the month and day they were born. Try it. You’ll love it!
[1] Yes, I know Postgres can index a function. So this can be considered a jab at MySQL, which can’t.
When reading this entry I was thinking about simply storing the “day of the year” value (0 to 365) but then I realized it could complicate things on leap years. If you store them separately, people born on a 29th of February shouldn’t be a problem with
SELECT *
FROM users
WHERE (birth_month, birth_day) BETWEEN (2, 27) AND (3, 6)
PS: your CAPTCHA is tricky. It asked me if a lion is a cat and it made check Wikipedia wondering if it was a trick question :)
Josh Davis
31 Dec 09 at 6:21 pm
I once wrote about it, including indexed way to search for people who have birthday “tomorrow” – it is for PostgreSQL, but shows some pitfalls, and possible solutions:
http://www.depesz.com/index.php/2007/10/26/who-has-birthday-tomorrow/
depesz
1 Jan 10 at 6:42 am
I played very long to find a fast solution for querys around the birthdate. Two weeks ago, I wrote about my GETAGE() function, and the first thing I tried to find friends which have birthday in a couple of days was writing a similar function with that body: DATE_ADD(bday, INTERVAL YEAR(FROM_DAYS(DATEDIFF(NOW(), bday) – 1)) + 1 YEAR)
I used this function inside of a trigger and for an update-routine to get a clean column for all of my friends that has birthday in up to one week. This worked for a while, but there are in theory $num_of_users / 365 birthdays per day. With an increasing $num_of_users you also have to update more rows – so I rewrote the system once more. A little intermediate step was using a VARCHAR colum with a REVERSE()’d DATE value to run a LIKE on it. This is also not the best solution because you need more code in the userspace, so I tried a DAY(bday)*100 + MONTH(bday) solution, which is really the best. Especially because you can write a little condition, so that all februry 29 can reduced by 100, if the current year does not have 29 days in that month.
Robert
15 Jan 10 at 12:03 pm