MySQL：最好使用什么，Unix TimeStamp 还是 DATETIME
可能很多程序员都想问这个问题。这是 MySQL 时间格式中每一种的优点是什么。以及您更喜欢在应用程序中使用哪一种。
对我来说，我使用 Unix 时间戳，因为也许我发现它很容易转换和转换。用它来订购记录，也是因为我从未尝试过 DATETIME 的东西。但无论如何，如果有人告诉我我错了，我准备改变主意。
谢谢
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.
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.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
时间戳（PHP 和 MySQL 的） 使用 32 位（即 4 个字节） 整数存储；这意味着它们仅限于从 1970 年到 2038 年的日期范围。
DATETIME没有这个限制 - 但使用更多字节存储（8 个字节，如果我不是错误）之后，在存储 PHP 看到的时间戳或 MySQL 看到的时间戳之间：
并且，有关 MySQL 的
TIMESTAMP和DATETIME数据类型之间的更多信息，请参阅 10.3.1. DATETIME、DATE 和 TIMESTAMP 类型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.
DATETIMEdon't have that limitation -- but are stored using more bytes (8 bytes, if I'm not mistaken)After, between storing timestamps as seen by PHP, or timestamps as seen by MySQL :
And, for more informations between MySQL's
TIMESTAMPandDATETIMEdatatypes, see 10.3.1. The DATETIME, DATE, and TIMESTAMP Types正如其他人所说，时间戳可以代表较小范围的日期时间（从 1970 到 2038）。但是，时间戳测量自 Unix Epoch (1970-01-01 00:00:00 UTC) 以来的秒数，从而使它们独立于时区，而 DATETIME 存储没有时区的日期和时间。换句话说，时间戳明确引用特定时间点，而 DATETIME 引用的确切时间点需要时区（不存储在 DATETIME 字段中）。要了解为什么这很重要，请考虑如果我们更改时区会发生什么。
假设我们要存储日期时间 2010-03-27 12:00 UTC。如果我们存储它并使用时间戳或 DATETIME 检索它，那么通常看起来没有区别。但是，如果服务器现在更改为本地时区为 UTC+01，那么如果我们提取日期时间，我们会得到两个不同的结果。
如果我们将该字段设置为 DATETIME，则尽管时区发生变化，它仍会将日期时间报告为 2010-03-27 12:00。如果我们将该字段设置为时间戳，则日期将报告为 2010-03-27 11:00。这对于任何一种数据类型来说都不是问题——这只是它们存储的信息略有不同的结果。
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.
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.
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 更好。< br>
例如，表格可能如下所示：
[session_create_time AS Timestamp][IP_address AS 32bit Int][etc...]在前两列上建立索引确实可以加快查询速度。如果
session_create_time字段具有 DATETIME 值类型，则可能会花费更多时间。考虑到每次用户请求页面时都会执行会话查询，因此效率至关重要。当您想要存储用户的出生日期或一些需要灵活时间范围的历史事件时，DATETIME 比 Timestamp 更好。
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 is better than Timestamp when you want to store a user's date of birth or some historic events that require flexible time range.
除非对 1970 年 1 月 1 日之前的记录进行数字化，否则我喜欢 UNIX 时代。这只是一个偏好问题，使用多种语言时，整个无符号数字更容易处理。
请记住，这个时代从 1970 年 1 月 1 日开始。在此之前，许多公司已经营业了几十年，甚至更久。
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.
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.