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?
Answer
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