storing dates in mysql

Is it better to store dates in mysql in three columns or use just one column开发者_StackOverflow社区. Which one is faster. Also, if I just want to do inserts with todays date in format dd/mm/yy , how do I do that. and also how do I do selects with that. Also, lets say if I wanted to get results for all the wednesdays, how do I do that or lets say one date 25th of all the months and years, how do i do that.

Thanks People.

I am using PHP with Apache and Mysql.

What are the drawbacks of using the structure that I am proposing. I can easily get all the 25th by using the date table and I can get all the days using another column for days. How much difference would be there in the terms of speed between my proposed solution and using a DATE table?

You will want to use a proper column type, such as DATE, DATETIME, or TIMESTAMP, depending on your needs. They are built specifically to handle dates, and can more easily perform other functions (adding, comparing, etc.) that would be difficult to perform on 3 separate columns.

Read this for more info.

DAYOFWEEK(date) will give you a numeric representation for the day. In your case, 4 = Wednesday. DAYOFMONTH(date) will work for finding all 25th days of the month.

DAYNAME(date) will return the name of the weekday for date

Also, if I just want to do inserts with todays date in format dd/mm/yy ,how do I do that.

Well it depends on the format your date is passed in through your 
form but you are going to want to store your date in YYYY-mm-dd format.

INSERT INTO my_table (timefieldname) VALUES ( '$date' );

and also how do I do selects with that.

SELECT timefieldname FROM my_table;

//or you can format the date - this will give you month/day/year 01/01/2012
SELECT DATE_FORMAT(timefieldname, '%m/%d/%Y') FROM my_table;

Also, lets say if I wanted to get results for all the wednesdays,

SELECT timefieldname FROM my_table WHERE DAYNAME(timefieldname) = 'Wednesday';

How do I do that or lets say one date 25th of all the months and years, how do i do that.

SELECT timefieldname FROM my_table WHERE DAY(timefieldname) = '25'; 

You can free up having to pass dates from your codebase and let mysql insert them for you, provided they are time stamps:

ALTER TABLE tablename ADD `timefieldname` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;

It's not much of a speed boost, but it does reduce your need to code and validate variables passed to the database.