What is the right way of logging members birthday in database?

Question

Hello, I am working on a project which needs to insert the member birthday's into MySQL database. But i am confused. Actually i am using it like that:

int(10)

So i change the format e.g. "27/06/1960" to unix time using PHP and use the function date() to show the birthday. Is there any better solution? and is there any good resource you recommend to read about "DATE, DATETIME, TIMESTAMP"?

Thanks.

Answer 1

I'd start with the DATETIME manual page for your database. In general, if a database has a specific column type designed for exactly the type of data you plan to store in that column, it's often a good idea to use it. Not only does it make it clearer exactly what is stored in the column, but it also potentially allows you to write more powerful queries, and let the DB engine optimize those for you.

Answer 2

There is no right and no wrong decision. There is only one way you like the most, and this is the right decision.

I really can say, it does not matter how design your database and code, as long as you follow your own design. So if you use a special date pattern, than use it through the code, so you won't struggle with thousands of converts.

E.g. HTML form pattern: DD/MM/YYYY

PHP pattern (exlpode('/',$date)): DD:MM:YYYY

DB pattern: DD-MM-YYYY

Answer 3

Use a DATE column. MySQL expects input as "YYYY-MM-DD" and outputs values like that.

A date that was not (yet) provided by user may be encoded as a NULL column. Though you may not need the extra bit that the "NULL" requires, you may even write the special value "0000-00-00" for that.

Answer 4

The fastest way to find out when somebody birthday is would be to use 2 or 3 seperate INT columns and probably an addition column to store the whole thing as a DATETIME. While you don't normally use ints nor multiple columns to represent date, keep in mind if you have a lot of rows in this table, doing something like

SELECT * FROM people WHERE birthday LIKE '%-12-24';

is not very efficient. It works fine in smaller datasets but because very slow if we start getting into tens of thousands or more (depends on your hardware). Sometimes you need to store data in unusual ways to keep your system efficient, the tiny amount of HD space you waste by having up to 4 sets of date columns (month, day, year, and store the whole thing as a datestamp) pays off in speed.

By doing this you can simply do:

SELECT * FROM people WHERE birth_month=12 AND birth_day=24