What is the best field to store the birthday?

Question

I have to store in my mysql database, the users's birthday ( like 12-11-1990 ).

What field type do I have to use to store it ?

Answer 1

to store a date, you should probably use a date. to quote the documentation:

The DATE type is used for values with a date part but no time part.

Answer 2

You can store it as a DATE object as you would normally do with non-repeating dates. The, if you're using MySQL (I believe other DBMS also have functions for this) you can retrieve birthdays using MySQL functions as described here:

SELECT * 
FROM table_name 
WHERE 
    MONTH(date_field) = desired_month AND 
    DAY(date_field) = desired_day

This method can be a bit slow when dealing with thousands of records. If that's your case, you can store the birthday with 3 separate INTs, one for each (year, month, day). Then you search birthdays like this:

SELECT *
FROM table_name
WHERE
   month_field = desired_month AND
   day_field = desired_day

If you use this last method, I'd advise you to also store a DATETIME version of the birthday so that you can build the date without any kind of maneuvers. You can additionally index each one of the 3 fields (non-unique) so it's faster to retrieve them.

Answer 3

"DATE" should be good.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

More details : http://dev.mysql.com/doc/refman/5.5/en/datetime.html

Answer 4

You should store the date as DATE.

And the Locale as a varchar (PHP or Java).

Don't forget the Locale :)

Nothing like getting an Email from something like Weibo (Chinese Twitter) when its not your birthday yet.

Answer 5

is DATE too obvious?