MySQL日期还是PHP时间? [英] MySQL date or PHP time?

问题描述

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

I usually store dates as integers using PHP's time() function in a MySQL database rather than using MySQL's date format simply because it's easier to manipulate when I pull it back out, but are there any disadvantages to this?

推荐答案

范围:

总是有一个明显的缺点:您可以存储的范围是从1970年到2038年.如果您需要存储超出此范围的日期，通常将需要使用另一种格式.我发现最常见的情况是生日.

There's always the obvious disadvantage: The range that you can store is limited från 1970 to 2038. If you need to store dates outside of this range, you'll generally need to use another format. The most common case I've found where this apply is to birthdates.

可读性:

我认为人们选择使用内置日期类型之一的最重要原因是数据更易于解释.您可以进行简单的选择，并了解值，而无需进一步格式化响应.

I think that the most important reason that people chose to use one of the built-in date-types it that the data is easier to interpret. You can do a simple select, and understand the values without having to format the response further.

索引:

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

A good technical reason to use the date types is that it allows for indexed query in some cases that unix timestamps doesn't. Consider the following query:

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

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

If mydate_field is of a native date type, and there's an index on the field, this query will actually use an index, despite the function call. This is pretty much the only time that mysql can optimize function calls on fields like this. The corresponding query on a timestamp field won't be able to use indices:

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

如果您稍微考虑一下，还是可以解决的.该查询执行相同的操作，并且 将能够使用索引优化:

If you think about it for a bit, there's a way around it, though. This query does the same thing, and will be able to use index optimizations:

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时间戳，则只需执行以下操作即可轻松完成:

Generally, I store dates as unix time, despite the disadvantages. This isn't really based on it's merits, but rather it's because I'm used to it. I've found that this simplifies some calculations, but complicate others. For example, it's very hard to add a month to a unix timestamp since the number of seconds per month varies. This is very easy using the mysql DATE_ADD() function. However, I think that in most cases it actually simplifies calculations. For example, it's quite common that you want to select the posts from, say, the last two days. If the field contains a unix timestamp this can be done easily by simply doing:

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

这可能是一个问题，但是我个人觉得这比必须重新编写DATE_SUB()之类的函数的语法更快，更容易.

It's probably a matter of taste, but I personally find this faster and easier than having to rember the syntax of a function such as DATE_SUB().

时区:

Unix时间戳不能存储时区数据.我住在瑞典，时区只有一个时区，所以这对我来说真的不是问题.但是，如果您居住在一个跨越多个时区的国家，则可能会感到很痛苦.

Unix timestamps can't store time zone data. I live in sweden which has a single timezone, so this isn't really a problem for me. However, it can be a major pain if you live in a country that spans multiple timezones.

这篇关于MySQL日期还是PHP时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！