Stay Curious!

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 indexes1.

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.

Posted on Thu, Dec 31, 2009. Approximately 200 Words.

Databases