When to use datetime or timestamp [duplicate]

Assuming you're using MS SQL Server (Which you're not, see the Update below):

A table can have only one timestampcolumn. The value in the timestampcolumn is updated every time a rowcontaining a timestamp column isinserted or updated. This propertymakes a timestamp column a poorcandidate for keys, especially primarykeys. Any update made to the rowchanges the timestamp value, therebychanging the key value. If the columnis in a primary key, the old key valueis no longer valid, and foreign keysreferencing the old value are nolonger valid. If the table isreferenced in a dynamic cursor, allupdates change the position of therows in the cursor. If the column isin an index key, all updates to thedata row also generate updates of theindex.

Information on MSDN

If you need to store date/time information against a row, and not have that date/time change, use DateTime; otherwise, use Timestamp.

Also Note: MS SQL Server timestamp fields are not Dates nor Times, they are binary representations of the relative sequence of when the data was changed.

Update

As you've updated to say MySQL:

TIMESTAMP values are converted fromthe current time zone to UTC forstorage, and converted back from UTCto the current time zone forretrieval. (This occurs only for theTIMESTAMP data type, not for othertypes such as DATETIME.)

Quote from MySQL Reference

More notably:

If you store a TIMESTAMP value, andthen change the time zone and retrievethe value, the retrieved value isdifferent from the value you stored.

So if you are using an application across timezones, and need the date/time to reflect individual users settings, use Timestamp. If you need consistency regardless of timezone, use Datetime

See Should I use field 'datetime' or 'timestamp'?It has a comprehensive coverage about the topic.

EDIT - Just to summarize properties for MySQL and my experience with it-

Timestamp -

a) 4 bytes per column (compared to 8 for datetime)

• LOWER RANGE ('1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC ) THAN DATETIME - So definitely don't use it for birthdates etc. Most usage patterns are to actually provide a 'Timestamp' of 'NOW' for activities like row updates, etc etc.

b) stored internally as an integer

• Performance wise... my personal experience has been ambiguous.. sometimes its faster... sometimes slower than DATETIME. It takes less space though.

c) Has timezone info!

• so - if I add '2011-01-01 3:30' in TIMESTAMP (with curr timezone as EST - Boston).. later, i change the server & mysql timezone to PST(california) and restart server - the value will change to '2011-01-01 00:00' -- (PLEASE CONFIRM... i had tested this a long time ago). However, DATETIME will remain the same.

d) All the DATE() / DAY() / MONTH() functions work for both TIMESTAMP and DATETIME

e) In MySQL, you can have multiple TIMESTAMPS per table

• (YES, however only one of them (the first) will be updated automatically with the time of row update, also... only one can be made NOT NULL (think the first))

f) first TIMESTAMP in a table is automatically updated...

• so be careful if you use it for some other purpose.. and want to allow nulls there. (null stored as '0000-00-00 00:00:00' in both DATETIME and TIMESTAMP)

I have used multiple timestamps for other purposes.. needed the space saved (had to be very careful and keep all these issues in mind.

My advice, go for TIMESTAMP for non timestamp purposes only if u know what u are doing.. and if SPACE is a huge concern (my eg - 15,000,000 rows and growing and 8 datetimes!))

I did not get your question clearly, but see below link. it may help you

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types