Storing dates in mysql: Performance comparison between various methods for Date

Question

Now there are three options that I have in my mind.

1st -> four columns(date, month, year, day) => 28, 03, 2011, 1 I can easily search and modify these columns without additional learning of mysql dates.

2nd -> one Date column(dd-mm-yyyy) => 28-03-2011 This only requires one column, easier to manage as there is only one WHERE parameter for searching dates. But I dont know how can I search all the records for say a particular day. Lets say all the data for all the mondays in the past or all the data for all the 28ths.

3rd -> two columns(unix timestamp for todays date, day) => 1827328721, 1 Now, here I can store store the data as a time stamp and easily do searches and comparisons by simple getting a date and then turning it into a unix timestamp and then using that in the sql. For day I can use the day column.

Now the questions are:

1. How does the performance differ between these methods?
2. How would the queries for selects, inserts and updates be constructed for these different proposed solutions?
3. Which one is best in your opinion and why?

Please answer all the three questions thoroughly. Because I can not find this information elsewhere, and I know stack overflow has brilliant programmers who can answer this very coherently. This question will not only benefit a newbie like me but think of all the other newbies who could use this for reference.

Thanking in advance to Stack overflow Community.

Answer 1

How does the performance differ between these methods?

This will vary greatly based on what type of application is using the database.

The first method could be considered a de-normalized approach, but this could be a good idea if the data is used for an OLAP application. For example, if you frequently need to gather massive sets of data for a specific day of the month, then this de-normalization could be justified... otherwise, it would just be a waste of storage and needlessly complex.

The same with the third option... it's just a de-normalized representation of the date; it could be justified in very limited uses, but usually it would be a bad idea.

--EDIT--

By De-normalized, I mean the following...

Let's say you have a record with the following fields...

Date       Day    Month    Year
3/28/2011  28     3        2011

And lets say you need to change the day from the 28th to the 29th. In this case, you need to update two fields, both the Date and the Day field... instead of just one. If you always remember to update both, then it isn't a huge problem. But if you don't, over time you end up with something like the following.

Date       Day    Month    Year
3/28/2011  29     2        2009

So what's the actual date? By storing the information in a single place, you eliminate the possibility of inconsistencies in the data.

-- END EDIT--

Which one is best in your opinion and why?

Unless your database is used for OLAP... I would consider the second option to be the best.

How would the queries for selects, inserts and updates be constructed for these different proposed solutions?

For the preferred, second option, this is trivial.

-- SECOND EDIT --

You can pass in the date as a string, and MySQL will parse it according. The date format is "YYYY-MM-DD HH:mm:SS", but you don't have to specify the time if you don't care to store it.

INSERT INTO tablename (date) VALUES ('2011-3-28')

Or, if you just want to add the current date...

INSERT INTO tablename (date) VALUES (CURDATE() )

-- END EDIT --