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

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

解决方案

假设您使用的是 MS SQL Server(您不是，请参阅下面的更新):

<块引用>

一张表只能有一个时间戳柱子.时间戳中的值每次一行都会更新列包含时间戳列是插入或更新.这个性质使时间戳列变差键的候选者，尤其是主键键.对行进行的任何更新更改时间戳值，从而改变键值.如果列在主键中，旧键值不再有效，外键引用旧值是否有效期更长.如果表是在动态游标中引用，所有更新改变了位置游标中的行.如果该列是在索引键中，所有更新数据行也生成更新索引.

MSDN 上的信息/p>

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

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

更新

正如你所说的 MySQL:

<块引用>

TIMESTAMP 值是从UTC 的当前时区存储，并从 UTC 转换回来到当前时区恢复.(这仅发生在TIMESTAMP 数据类型，不适用于其他DATETIME 等类型.)

引用自 MySQL 参考

更值得注意的是:

<块引用>

如果您存储一个 TIMESTAMP 值，并且然后更改时区并检索值，检索到的值为与您存储的值不同.

因此，如果您跨时区使用应用程序，并且需要日期/时间来反映各个用户的设置，请使用时间戳.如果您需要不考虑时区的一致性，请使用 Datetime

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

解决方案

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