MySQL日期还是PHP时间？

Question

我通常使用PHP的time()函数在MySQL数据库中将日期存储为整数，而不是使用MySQL的日期格式，因为当我将其取出时更容易操作，但这样做有什么缺点吗？

- mpen

Answer 1

范围：

显然的缺点是：你可以存储的日期范围从1970年到2038年有限。如果需要存储这个范围之外的日期，通常需要使用另一种格式。我发现最常见的情况是出生日期。

可读性：

我认为人们选择使用内置日期类型的最重要原因是数据更容易解释。你可以进行简单的查询，不必进一步格式化响应就能理解值。

索引：

使用日期类型的一个好的技术原因是它允许在某些情况下进行索引查询，而unix时间戳则不行。考虑以下查询：

SELECT * FROM tbl WHERE year(mydate_field) = 2009;

如果mydate_field是本地日期类型，并且该字段上有索引，则此查询实际上将使用索引，尽管有函数调用。这几乎是mysql唯一可以优化这种字段的函数调用的时间。对于时间戳字段的相应查询将无法使用索引：

SELECT * FROM tbl WHERE year(from_unixtime(mytimestamp_field)) = 2009;

如果你仔细考虑一下，其实是有个解决方法的。这个查询语句能够完成相同的任务，而且可以利用索引优化：

SELECT * FROM tbl WHERE mytimestamp_field > unix_timestamp("2009-01-01") AND mytimestamp_field < unix_timestamp("2010-01-01");

计算：

通常情况下，我将日期存储为Unix时间戳，尽管这样做有一些不利之处。这并不是因为它本身的优点，而是因为我习惯了这种方式。我发现这简化了一些计算，但同时也使其他计算变得复杂。例如，要在Unix时间戳上加一个月非常困难，因为每个月的秒数是不同的。使用mysql DATE_ADD()函数很容易实现这一点。然而，在大多数情况下，我认为它确实简化了计算。例如，通常你可能会想要选择最近两天内的帖子。如果字段包含Unix时间戳，则只需轻松执行以下操作：

SELECT * FROM tbl WHERE mytimestamp_field > time() - 2*24*3600;

可能是个人口味问题，但我个人认为这种方法比记忆 DATE_SUB() 函数的语法更快更容易。

时区:

Unix 时间戳无法存储时区数据。我住在瑞典，只有一个时区，所以这对我来说不是真正的问题。然而，如果你住在横跨多个时区的国家，这可能会带来很大的麻烦。

- Emil H

---

我只想说“+1”表示赞同你提到UNIX_TIMESTAMP和FROM_UNIXTIME函数...这本质上给了你最好的两个选择。 - Narcissus

本来想收集更多答案后再回来给你检查，谢谢！这是一个很好的答案。 - mpen

Answer 2

一个缺点是您将无法使用SQL函数操作和查询这些日期。

- amarillion

Answer 3

我曾经也这样做，但现在我将它储存为MySQL DateTime——因为这意味着当查看数据库中的原始数据时，我可以轻松解释它。

除此之外，使用其他语言处理数据可能更容易（因为它们不像PHP那样大量使用UNIX时间戳），但两种方式之间没有太大的差异。

- Rob Golding

Answer 4

UNIX时间戳在能够存储的日期范围方面具有明显的限制。

我现在总是使用DATETIME字段。您可以使用SQL进行大量的日期计算，因此您可以提取有用的信息，例如现在和存储日期之间的DATEDIFF，而无需使用任何PHP。

- Chris Thompson

Answer 5

您可以在表定义中为MySQL的时间戳定义自动更新条款。
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

- VolkerK

Answer 6

存在许多缺点：

• 精度不够；Unix时间只能精确到秒，而且仅限于使用典型32位整数时介于1901年12月13日和2038年01月19日之间的日期
• 无法使用任何内置数据库函数来查询或操作数据
• 无法存储时区

如果需要time_t，可以在代码中轻松转换。

- Steve Madsen

Answer 7

我只能想到以下几点：
* 如果其他非 PHP 应用程序需要使用数据库，那么这将是一种难以阅读的格式。
* 如果您想对这些日期进行任何基于 SQL 的工作（例如添加一个月或获取特定年份的所有值等），那么这将更加困难。

- Chris Simpson

---

几乎所有常见的编程语言都内置了对纪元时间的支持。 - Emil H

Answer 8

细节上稍有损失。MySQL Datetime变量可以非常精确。

此外，如果你需要在数据库中比较日期，日期格式有一些内置函数是你无法使用的。

- KdgDev

---

我想看一下你声称DATETIME更精确的参考资料。 - Emil H

你的意思是更精确地使用time()函数吗？1）http://be2.php.net/manual/en/function.time.php和2）http://dev.mysql.com/doc/refman/5.1/en/datetime.html Time()函数精确到秒，MySQL DateTime精确到微秒。 - KdgDev

Answer 9

我认为出于可扩展性的原因，最好使用Unix时间戳。

优点:

• 始终存储在UTC时区(如果您有跨多个时区的服务器，则无需进行转换)。
• 应用程序将其转换为首选时区(仅在可能的最后一级发生一次)。
• 不是字符串(与整数相比，字符串很大)。
• 较少的数据库计算(例如，created < 19345345345-24*60*60只计算一次)。

编辑： MySQL时间戳不会在内部存储为字符串，但是当从数据库中提取它们时，它们会被转换为字符串。 DATETIME类型不会被MySQL修改，这意味着如果您将日期放入数据库中，则会得到相同的日期。

如果您的网站有来自不同时区的访问者，您需要将日期转换为字符串而不是整数。在一些国家，日期不仅仅是数字（例如在法国，日期格式为“Mardi 15 mai 2012”）。我更喜欢使用PHP或JS进行转换，因为我认为简单的整数转字符串比整数转字符串再转字符串要快。此外，如果迁移到其他国家的服务器，也不会出现任何问题。

虚假缺点：

• 我认为Unix时间戳的范围并不是真正的限制。时间戳是数据库中的整数，因此可以调整大小。默认情况下，unsigned整数是int(10)，意味着您可以存储高达4294967295的数字，但它的限制并不固定，因此我们可以轻松地将int(10) int更改为int(16) bigint

- Aalex Gabi

---

我认为它们实际上并没有作为字符串存储在内部。而且我不明白它如何减少数据库计算... date_add(my_date, interval 2 months) 应该只被计算一次，但无论如何都可以在 PHP 端完成。关于时区的事情我不确定... MySQL 时间戳是否嵌入了时区或者是全局的数据库设置？ - mpen

在MySQL中，这些时间戳被存储为UTC格式，并根据客户端时区进行提取。根据MySQL 文档，“TIMESTAMP列的值在存储时从当前时区转换为UTC，在检索时从UTC转换为当前时区。” - Aalex Gabi

好的，我现在明白你的观点了。如果你将MySQL设置为以UTC离开，那么时区不应该是一个问题，如果你想在PHP端进行转换，但是字符串->整数->字符串优化是一个有效的观点，尽管我怀疑它并不是一个很大的成本。你要解析什么？也许是100个日期在一页上？ - mpen

我更喜欢这样做，因为我相信出错的可能性更小，所以无论我在做什么项目，都是按照“正确的方式”或“我的正确方式”完成的。我相信有一种好的方法来做这件事，如果没有，而且它更具任务特定性，我希望能发现这些特定性。关于这些类型转换的成本，我认为避免它们是没有成本的。 - Aalex Gabi

我认为在这里没有一个“正确的方法”。一种解决方案最多比另一种略好一些，但两者都不是“错误”或“不良实践”。我最近发现 Unix 时间戳的一个缺点是 MySQL 的 UNIX_TIMESTAMP() 似乎不能处理负数（至少不是没有一些技巧）。 - mpen

Answer 10

这还不错，但您将失去一些内置功能，例如：

select * from table1 where dateColumn = getDate()-30

如果可以，请使用datetime！

- jr3