Storing dates in mysql: comparing performance between different methods for Date

Question

Now I have three options.

1st → four columns (date, month, year, day) => 28, 03, 2011, 1 I can easily search and change these columns without further studying mysql dates.

2nd → one column Date (dd-mm-yyyy) => 03/28/2011 This requires only one column, which is easier to manage since there is only one WHERE parameter to search for dates. But I don’t know how I can search all records for a certain day. Let's say all the data for all Mondays in the past or all the data for all 28 years.

3rd → two columns (unix timestamp for today's date, day) => 1827328721, 1 Now I can store the data as a timestamp and easily search and match just by getting the date and then turning it into a unix timestamp and then using it in sql. During the day, I can use the column of the day.

Now the questions are :

• How is the performance of these methods different?
• How will queries be created to select, insert, and update for these various proposed solutions?
• Which one is best in your opinion and why?

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

Thanks in advance to the community.

Answer 1

How is the performance of these methods different?

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

The first method can be considered an irregular approach, but it might be a good idea if the data is used for an OLAP application. For example, if you often have to collect massive data on a certain day of the month, then this normalization may be justified ... otherwise it would be just a waste of storage and without unnecessary difficulties.

Same thing with the third option ... it's just a de-normalized representation of a date; this may be justified in very limited use, but usually it would be a bad idea.

- EDIT -

De-normalized, I mean the following ...

Say you have an entry with the following fields ...

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

And let's say you need to change the day from the 28th to the 29th. In this case, you need to update two fields: the "Date" and "Day" ... instead of one. If you always remember to upgrade both, then this is not a huge problem. But if you do not, in time you will get something like the following.

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

So what is the actual date? By storing information in one place, you eliminate the possibility of data inconsistency.

- END EDIT -

Which one is best in your opinion and why?

If your database is not used for OLAP ... I would consider the second option as the best.

How will queries to select, insert, and update built for these proposed solutions be carried out?

For the preferred second option, this is trivial.

- SECOND EDIT -

You can pass the date as a string, and MySQL will parse it according to. The date format is "YYYY-MM-DD HH: mm: SS", but you do not need to specify the time if you do not want 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 -

Answer 2

If you need to store date values, the date (or datetime ) type is your choice. Mysql has many date-time functions that help you build queries. For instance,

 SELECT * FROM your_table WHERE date_column < DATE(NOW()) AND DAYOFWEEK(date_column) = 2 

// all mondays in the past

Answer 3

• Useful only if you need these parts that are unlikely.
• Date or date-time are native, and you can make them an index to speed up.
• Not good, not for mysql. if you need resolution up to a few seconds, use datetime.

See the manual for more information on working with date and date.