When to use datetime or timestamp [duplicate]

Question

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

Answer 1

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

Answer 2

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!))