86

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

James P.
  • 19,313
  • 27
  • 97
  • 155
  • "When to use datetime or timestamp?" does not have the same meaning as "Should I use field 'datetime' or 'timestamp'?". Not sure why it's been marked as duplicate. – James P. Oct 03 '17 at 20:21

5 Answers5

72

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

Community
  • 1
  • 1
Jaymz
  • 6,140
  • 2
  • 26
  • 30
  • Thanks for the reply. What exactly is meant by the timestamp column being updated? Does this mean that all values in a column are changed? – James P. May 13 '11 at 09:05
  • 1
    It means that the value for **the row being edited** would be changed - note: that is for MS SQL only; I've updated the answer to include MySQL information. – Jaymz May 13 '11 at 09:07
  • Ok, thanks for the update. So to reformulate, a timestamp value adapts to a change of timezone setting or when data is imported on a server with a different timezone? – James P. May 13 '11 at 09:11
  • 4
    Kind of, they are converted to UTC before being stored, and then converted back to the current time zone settings on retrieval. So if time zone settings change between storage and retrieval, you will get back different values; although still corresponding to the same UTC datetime. – Jaymz May 13 '11 at 09:14
  • Sounds like a logical approach. I appreciate the clarification. – James P. May 13 '11 at 09:20
31

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

Community
  • 1
  • 1
Jai
  • 3,549
  • 3
  • 23
  • 31
  • Thanks Jai. I have already skimmed through the topic but it seems to be more about the technical differences between the two (quite confusing I'll add) rather than what cases they should be used for. See unbeknown's reply for what I'm looking for. – James P. May 13 '11 at 09:09
  • 1
    @James P. just added my personal experience with both – Jai May 13 '11 at 09:58
  • 1
    Point c) is confusing. The point is that you WANT to use timestamps, because they are based on a single universal time all over the world; whereas DateTimes are merely local, and so subject to opinions as to which time zone you're in. The current explanation makes it look backwards. 4:30 in Boston will translate as 1:30 in California, with timestamps, because they are actually the same time. This is what you want, esp. if you are doing banking. With DateTimes, the timezone information is not taken into account; so 4:30 in Boston will also be given as 4:30 in California. Oops. – DragonLord Jun 10 '13 at 21:15
6

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

Bhushan Kawadkar
  • 28,279
  • 5
  • 35
  • 57
3
  • 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.

kapa
  • 77,694
  • 21
  • 158
  • 175
Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
  • 1
    Timestamp can hold values before 1970 - you just use a negative integer. Also, if you use an unsigned int, you can go past 2038. – Camilo Martin Jan 20 '13 at 08:24
2

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

sehe
  • 374,641
  • 47
  • 450
  • 633
  • Edited the question. This is for MySQL. – James P. May 13 '11 at 09:03
  • 2
    In that case, [google first hit](http://www.google.com/search?client=opera&rls=en&q=mysql+timestamp&sourceid=opera&ie=utf-8&oe=utf-8&channel=suggest) will tell you that [TIMESTAMP in mysql offers automatic updating and converts to UTC time](http://dev.mysql.com/doc/refman/5.0/en/timestamp.html) regardless of the connection timezone. You need to explicitely sepcify `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` in the column creation though – sehe May 13 '11 at 09:06
  • Ok, so timestamp should be used when an application is used accross timezones. Are there any other cases that come to mind where it can be used? – James P. May 13 '11 at 09:14
  • No. Timestamp would be used for versioning of records. The UTC conversion is only instrumental to that end, so comparisons are efficient and safe always. Did you read the wikipedia link? If you don't know what I'm on about, you probably don't need this anyway. Do you use explicit locking/transaction isolation? Forget this then – sehe May 13 '11 at 09:14
  • Ah, it appears there are different opinions on how it should be used. Yes, I understand the concept of optimistic locking and, no, I don't use anything of the sort. – James P. May 13 '11 at 09:19
  • Ah... well on reading the docs one more time, it appears that there may be a usecase for TIMESTAMP just for timezone normalization. I was a bit rash there because I was reiterating my knowledge of TIMESTAMP columns on multiple database engines, instead of reading the MYSQL specific bits. I think you have enough info to work things out :) Thx – sehe May 13 '11 at 09:22