php MySQL：最好使用什么，Unix TimeStamp 或 DATETIME

MySQL: What's the best to use, Unix TimeStamp Or DATETIME

提问by Ryan

Probably many coders want to ask this question. it is What's the adventages of each one of those MySQL time formats. and which one you will prefer to use it in your apps.

可能很多程序员都想问这个问题。这些 MySQL 时间格式中的每一种都有什么优点。以及您更喜欢在您的应用程序中使用哪一个。

For me i use Unix timestamp because maybe i find it easy to convert & order records with it, and also because i never tried the DATETIME thing. but anyways i'm ready to change my mind if anyone tells me i'm wrong.

对我来说，我使用 Unix 时间戳是因为我发现用它转换和排序记录很容易，而且我从来没有尝试过 DATETIME。但无论如何，如果有人告诉我我错了，我已经准备好改变主意了。

Thanks

谢谢

采纳答案by Pascal MARTIN

Timestamp (both PHP ones and MySQL's ones)are stored using 32 bits (i.e. 4 bytes)integers ; which means they are limited to a date range that goes from 1970 to 2038.

时间戳（PHP 的和 MySQL 的）使用 32 位（即 4 字节）整数存储；这意味着它们仅限于从 1970 年到 2038 年的日期范围。

DATETIMEdon't have that limitation -- but are stored using more bytes (8 bytes, if I'm not mistaken)

DATETIME没有那个限制——但是使用更多字节存储（8 个字节，如果我没记错的话）

After, between storing timestamps as seen by PHP, or timestamps as seen by MySQL :

之后，在存储 PHP 所见的时间戳或 MySQL 所见的时间戳之间：

• using PHP timestamps means manipulations are easier from PHP -- see Date/Time Functions
• using MySQL's timestamps means manipulations are easier from MySQL -- see 11.6. Date and Time Functions

• 使用 PHP 时间戳意味着从 PHP 操作更容易——请参阅日期/时间函数
• 使用 MySQL 的时间戳意味着从 MySQL 操作更容易——见11.6。日期和时间函数

And, for more informations between MySQL's TIMESTAMPand DATETIMEdatatypes, see 10.3.1. The DATETIME, DATE, and TIMESTAMP Types

并且，有关 MySQLTIMESTAMP和DATETIME数据类型之间的更多信息，请参见10.3.1。DATETIME、DATE 和 TIMESTAMP 类型

回答by Michael Williamson

As others have said, timestamps can represent a smaller range of datetimes (from 1970 to 2038). However, timestamps measure the number of seconds since the Unix Epoch (1970-01-01 00:00:00 UTC), thereby making them independent of time zone, whereas DATETIME stores a date and time without a time zone. In other words, timestamps unambiguously reference a particular point in time, whereas the exact point in time a DATETIME refers to requires a time zone (which is not stored in a DATETIME field). To see why this can matter, consider what happens if we change our time zone.

正如其他人所说，时间戳可以代表较小范围的日期时间（从 1970 年到 2038 年）。然而，时间戳测量自 Unix 纪元 (1970-01-01 00:00:00 UTC) 以来的秒数，从而使它们独立于时区，而 DATETIME 存储没有时区的日期和时间。换句话说，时间戳明确引用特定时间点，而 DATETIME 引用的确切时间点需要时区（未存储在 DATETIME 字段中）。要了解为什么这很重要，请考虑如果我们更改时区会发生什么。

Let's say we want to store the datetime 2010-03-27 12:00 UTC. If we store this and retrieve it using a timestamp or DATETIME, then there usually appears to be no difference. However, if the server now changes so that the local time zone is UTC+01, then we get two different results if we pull out the datetime.

假设我们要存储日期时间 2010-03-27 12:00 UTC。如果我们存储它并使用时间戳或 DATETIME 检索它，那么通常看起来没有区别。但是，如果服务器现在更改为本地时区为 UTC+01，那么如果我们拉出日期时间，我们会得到两种不同的结果。

If we'd set the field to a DATETIME, it would report the datetime as 2010-03-27 12:00, despite the change in time zone. If we'd set the field to a timestamp, the date would be reported as 2010-03-27 11:00. This isn't a problem with either datatype -- it's just a result of the fact that they store slightly different information.

如果我们将该字段设置为 DATETIME，它将报告日期时间为 2010-03-27 12:00，尽管时区发生了变化。如果我们将该字段设置为时间戳，则日期将报告为 2010-03-27 11:00。这对于任何一种数据类型都不是问题——这只是因为它们存储的信息略有不同。

回答by Dor

That really depends. I'll give you 2 examples where one overcome the other:

这真的取决于。我会给你两个例子，其中一个克服另一个：

Timestamp is better than DATETIME when you want to store users session in the database and the session creation time (in Timestamp format) is used for fast row retrieval (with index).
E.g. table may look like this:
[session_create_time AS Timestamp][IP_address AS 32bit Int][etc...]
Having an index on the first two columns can really speed up your queries. If you had a DATETIME value type for the session_create_timefield, then it could be taken much more time. Take into account that session queries are executed each time a user request a page, so efficiency is crucial.

当您想在数据库中存储用户会话并且会话创建时间（以时间戳格式）用于快速行检索（带索引）时，时间戳比 DATETIME 更好。
例如，表可能看起来像这样：
[session_create_time AS Timestamp][IP_address AS 32bit Int][etc...]
在前两列上建立索引可以真正加快您的查询速度。如果该session_create_time字段具有 DATETIME 值类型，则可能需要更多时间。考虑到每次用户请求页面时都会执行会话查询，因此效率至关重要。

DATETIME is better than Timestamp when you want to store a user's date of birth or some historic events that require flexible time range.

当您要存储用户的出生日期或一些需要灵活时间范围的历史事件时，DATETIME 比 Timestamp 更好。

回答by Tim Post

Unless digitizing records prior to January 1, 1970, I like the UNIX epoch. Its just a matter of preference, whole unsigned numbers are simpler to deal with when using multiple languages.

除非将 1970 年 1 月 1 日之前的记录数字化，否则我喜欢 UNIX 时代。这只是一个偏好问题，使用多种语言时，整个无符号数更容易处理。

Just keep in mind, the epoch starts at January 1, 1970. A lot of companies had been in business for decades, if not longer, prior to that.

请记住，这个时代始于 1970 年 1 月 1 日。在此之前，许多公司已经经营了几十年，如果不是更长的话。