When to use date and time

Question

I searched for this, but did not give clear answers (especially in the latter). When should a date or time be used?

Answer 1

Assuming you are using MS SQL Server (which is not, see Update below ):

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

MSDN Information

If you need to store date / time information in a string and not change the date / time, use DateTime; otherwise use a timestamp.

Also note:. MS SQL Server timestamps are not dates and times; they are binary representations of the relative sequence when data was changed.

Update

How did you update to say MySQL:

TIMESTAMP values ​​are converted from the current time zone for UTC to store and convert from UTC to the current time zone for search. (This only happens for the TIMESTAMP data type, not for the other DATETIME types.)

Quote from MySQL Reference

More noticeably:

If you save the TIMESTAMP value and then change the time zone and get the value, the resulting value is different from the value you saved.

So, if you use the application in time zones and you need to specify a date / time to reflect individual user settings, use Timestamp. If you need consistency, regardless of time zone, use Datetime

Answer 2

See Should I use the 'datetime' or 'timestamp' field? It has comprehensive coverage of the topic.

EDIT - Just to summarize the properties for MySQL and my experience with it -

Timestamp -

a) 4 bytes per column (compared to 8 for date and time)

• LOWER RANGE ('1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC) THAN DATETIME - So definitely not use it for birth dates, etc. templates should actually provide a "timestamp" for "NOW" for actions such as updating strings, etc. etc.

b) stored internally as a whole

• Performance is wise ... my personal experience was mixed .. sometimes its faster ... sometimes slower than DATETIME. It takes up less space though.

c) Has time zone information!

• so - if I add '2011-01-01 3:30' to TIMESTAMP (with time zone in hours like EST - Boston). Later, I change the mysql server and timezone to PST (california) and reboot the server - the value will change to "2011-01-01 00:00" - (PLEASE CONFIRM ... I have experienced this for a long time). However, DATETIME will remain the same.

d) All DATE () / DAY () / MONTH () functions work for both TIMESTAMP and DATETIME

e) In MySQL, you can have multiple TIMESTAMPS for a table

• (YES, however, only one of them (the first) will be automatically updated with the time the line was updated, also ... only one can be made NOT NULL (I think the first))

f) the first TIMESTAMP in the table is automatically updated ...

• So be careful if you use it for other purposes .. and want to allow null there. (null is stored as "0000-00-00 00:00:00" in both DATETIME and TIMESTAMP)

I used several timestamps for other purposes. The necessary space has been saved (must be very careful and take into account all these problems.

My advice, go to TIMESTAMP for timeless purposes only if you know what you are doing .. and if SPACE is a huge concern (my eg are 15,000,000 rows and grow and 8 datetimes!))

Answer 3

I did not understand your question clearly, but see the link below. it can help you

http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

Answer 4

You must specify the database server.

Some server mechanisms automatically update the timestamp fields, so they can be used as the version of the entry in Optimistic blocking.

Answer 5

• In MySQL, in DateTime you can work with DATE() related functions, while you cannot timestamp .
• timestamp cannot save values ​​until 01-01-1970 .
• In addition, one of them has daily savings, while others do not (I don’t remember which one now).

I usually choose DateTime .