Best way to format a date for storage in MySQL

[ATLChris]

What would be the best way to store a date in a MySQL database?

 

i.e. 20090209? 02092009? Unix Timestamp?

[dreamwest]

Almost..

 

2009-02-10

 

And update with

 

$sql = "UPDATE date SET adddate='".date( "Y-m-d" )."';
mysql_query($sql) or die(mysql_error());

[PFMaBiSmAd]

Use a DATE data type - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-type-overview.html

[aschk]

There are 2 options, use an integer timestamp or a date timestamp.

Either way your columns will be  INTEGER or DATETIME.

 

Now, there is a problem with using the built in NOW() and CURRENT_TIMESTAMP options in MySQL, and that's that they don't adhere to DST. Meaning you could end up hitting a date on the wrong side of DST. Thus while I DO condone the usage of CURRENT_TIMESTAMP and the TIMESTAMP field type, I recommend that you utilise the strtotime() function from PHP to generate a timestamp in INTEGER format, and if you prefer to have DATETIME's in your DB then use:

date('Y-m-d H:i:s', strtotime("NOW"));

[fenway]

Is that true even if you've set the server/client time zones appropriately? I haven't tried...