MySQL 何时使用日期时间或时间戳

When to use datetime or timestamp

提问by James P.

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

我已经搜索过这个，但没有明确的答案（尤其是后者）。在什么情况下应该使用日期时间或时间戳？

采纳答案by Jaymz

Assuming you're using MS SQL Server (Which you're not, see the Update below):

假设您使用的是 MS SQL Server（您不是，请参阅下面的更新）：

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

MSDN 上的信息

If you need to store date/time information against a row, and not have that date/time change, use DateTime; otherwise, use Timestamp.

如果您需要针对一行存储日期/时间信息，并且该日期/时间没有更改，请使用 DateTime；否则，使用时间戳。

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.

另请注意：MS SQL Server 时间戳字段既不是日期也不是时间，它们是数据更改时间的相对顺序的二进制表示。

Update

更新

As you've updated to say MySQL:

正如您已更新为 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.)

TIMESTAMP 值从当前时区转换为 UTC 进行存储，并从 UTC 转换回当前时区以进行检索。（这仅适用于 TIMESTAMP 数据类型，不适用于其他类型，例如 DATETIME。）

Quote from MySQL Reference

引用自MySQL 参考

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.

如果您存储 TIMESTAMP 值，然后更改时区并检索该值，则检索到的值与您存储的值不同。

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

因此，如果您跨时区使用应用程序，并且需要日期/时间来反映单个用户设置，请使用时间戳。如果无论时区如何都需要一致性，请使用 Datetime

回答by Jai

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-

编辑 - 只是为了总结 MySQL 的属性和我的经验 -

Timestamp -

时间戳 -

a) 4 bytes per column (compared to 8 for datetime)

a) 每列 4 个字节（日期时间为 8 个字节）

• 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.

• 低于 DATETIME 范围（'1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC） - 所以绝对不要将它用于生日等。大多数使用模式是实际上为行更新等活动提供“现在”的“时间戳”。

b) stored internally as an integer

b) 在内部存储为整数

• Performance wise... my personal experience has been ambiguous.. sometimes its faster... sometimes slower than DATETIME. It takes less space though.

• 性能明智......我的个人经验一直模棱两可......有时它更快......有时比DATETIME慢。不过它占用的空间更小。

c) Has timezone info!

c) 有时区信息！

• 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.

• 所以 - 如果我在 TIMESTAMP 中添加“2011-01-01 3:30”（当前时区为 EST - 波士顿）。稍后，我将服务器和 mysql 时区更改为 PST（加利福尼亚州）并重新启动服务器 - 值将更改到'2011-01-01 00:00'--（请确认......我很久以前就已经测试过了）。但是，DATETIME 将保持不变。

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

d) 所有 DATE() / DAY() / MONTH() 函数都适用于 TIMESTAMP 和 DATETIME

e) In MySQL, you can have multiple TIMESTAMPS per table

e) 在 MySQL 中，每个表可以有多个 TIMESTAMPS

• (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))

• （是的，但是只有其中一个（第一个）会随着行更新的时间自动更新，而且......只有一个可以设为 NOT NULL（想想第一个））

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

f) 表中的第一个 TIMESTAMP 会自动更新...

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

• 所以如果你将它用于其他目的，请小心..并希望在那里允许空值。（null 在 DATETIME 和 TIMESTAMP 中存储为“0000-00-00 00:00:00”）

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

我的建议，只有当你知道你在做什么时，才为非时间戳目的使用 TIMESTAMP ......并且如果空间是一个巨大的问题（我的例子 - 15,000,000 行和增长和 8 个日期时间！））

回答by Bhushan Kawadkar

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

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

回答by Tudor Constantin

• In MySQL, on DateTimetype you can work with DATE()related functions, whereas on timestampyou can't.
• Timestampcan 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)

• 在 MySQL 中，在DateTimetype 上可以使用DATE()相关函数，而在type上timestamp则不能。
• Timestamp不能保持之前的值01-01-1970。
• 此外，其中一个拥有夏令时，而其他人则没有（我现在不记得是哪一个）

I tend to always choose DateTime.

我倾向于总是选择DateTime。

回答by sehe

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

一些服务器引擎会自动更新时间戳字段，因此它可以用作乐观锁中的记录版本