When to use datetime or timestamp

I've searched for this but no clear answers (especially on the latter). In what cases should you use a datetime or timestamp?

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

> A table can have only one timestamp > column. The value in the timestamp > column is updated every time a row > containing a timestamp column is > inserted or updated. This property > makes a timestamp column a poor > candidate for keys, especially primary > keys. Any update made to the row > changes the timestamp value, thereby > changing the key value. If the column > is in a primary key, the old key value > is no longer valid, and foreign keys > referencing the old value are no > longer valid. If the table is > referenced in a dynamic cursor, all > updates change the position of the > rows in the cursor. If the column is > in an index key, all updates to the > data row also generate updates of the > index.

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 from > the current time zone to UTC for > storage, and converted back from UTC > to the current time zone for > retrieval. (This occurs only for the > TIMESTAMP data type, not for other > types such as DATETIME.)

Quote from MySQL Reference

More notably:

> If you store a TIMESTAMP value, and > then change the time zone and retrieve > the value, the retrieved value is > different 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 https://stackoverflow.com/questions/409286/datetime-vs-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

Need to specify database server.

Some server engines will automatically update the timestamp fields, so it can be used as record version in Optimistic Locking

• In MySQL, on DateTime type you can work with DATE() related functions, whereas on timestamp you can't.
• Timestamp can not hold values before 01-01-1970.
• Also, one of them holds the daylight savings and other don't (I don't remember which one right now)

I tend to always choose DateTime.