datetime, timestamp and int(11)

[Kemik]

Hello all,

 

I'm making a table to post news to my website. I need to store the time and date the article was created but I'm confused as to which data type to use in the MySQL database.

 

My PHP book suggests datetime, tutorials I've read say int(11) and then there's the one that's left over, timestamp.

 

Which do you usually choose in this situation and why?

 

Thanks.

[dcp]

Bear in mind that a timestamp will be updated each time the record is changed. If you post a news item and then a week later correct a typo, the item's timestamp will be altered. Worse, if you're tracking downloads in the same table, each download will trigger a change in the timestamp. Not good if you're sorting your data by the timestamp. That said, timestamp is nice for recording the date an time an item was entered into the database: because the db inserts the timestamp, you don't have to include it in your query. One drawback of using timestamp is that if you wish to output the data to the screen in any format other than the timestamp format (YYYY-MM-DD HH-MM-SS) you'll have to convert the date with strtotime() and then date().

 

I sometimes allow those entering content to select their own date. this is helpful if your date is really a "publish date" or you want to modify the date in order to control sorted display. In this case datetime is probably better, but for many of my "news" tables, which aren't being updated too often, just date works fine, as well. 

[clearstatcache]

we used to use int(11) in our date field...more convenient to used on our side....

[fenway]

Only use DATETIME... you'll thank me later.