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