从 PHP 在 MySQL 中存储日期的最佳实践

Best practice for storing the date in MySQL from PHP

提问by treznik

I've been using the unix timestamp all my life.

我一生都在使用 unix 时间戳。

I like it because it's easy to compare, it's fast because I store it as an integer. And since I'm using PHP, I can get any date/time format with date() function from the unixtimestamp.

我喜欢它因为它很容易比较，它很快因为我将它存储为一个整数。由于我使用的是 PHP，我可以从 unixtimestamp 中使用 date() 函数获取任何日期/时间格式。

Now, some people are saying that it's best to use the DATETIME format. But besides the more suited name, I don't see any advantages.

现在，有人说最好使用 DATETIME 格式。但除了更合适的名字，我看不出有什么好处。

Is it indeed better to use DATETIME, if so, what are the advantages?

使用DATETIME确实更好吗，如果可以，有什么好处？

Thanks.

谢谢。

回答by Gav

If you store dates as Unix timestamps in the database, you're giving yourself the heavy lifting. You have to convert them to the formats you want to use, you have to do the calculations between date ranges, you have to build the queries to get data in a range. This seems counter-intuitive- surely your "programmer time" is best spent solving real problems?

如果您将日期作为 Unix 时间戳存储在数据库中，那么您将承担繁重的工作。您必须将它们转换为您想要使用的格式，您必须在日期范围之间进行计算，您必须构建查询以获取范围内的数据。这似乎违反直觉——你的“程序员时间”肯定最好花在解决实际问题上吗？

It seems much better practice to store dates and times in the proper format that MySQL has available, then use the database functions to create the queries for the data you want. The time you would waste doing all the convertions and mucking about is massive compared to the afternoon spent reading (and understanding) 11.6 MySQL Date and Time Functions

以 MySQL 可用的正确格式存储日期和时间似乎更好的做法，然后使用数据库函数为所需数据创建查询。与花在阅读（和理解）11.6 MySQL Date and Time Functions的下午相比，您浪费在所有转换和处理上的时间是巨大的

回答by coderama

I've also been a huge fan of the unix timestamp all my life. But I think the correct answer is: "depends". I recently did a single table database where I wanted to only list URLs. There would be a date field, but the date field is purely for sorting. I.e order by last_crawled. Which means I will never use any built-in date functions on that field. It is merely an easy way to get the oldest entries first and I will never apply date functions to this field. Now, had I made this a date field, I would have lost out on two things:

我也一直是 unix 时间戳的忠实粉丝。但我认为正确的答案是：“取决于”。我最近做了一个单表数据库，我只想列出 URL。会有一个日期字段，但日期字段纯粹是为了排序。即按 last_crawled 排序。这意味着我永远不会在该字段上使用任何内置日期函数。这只是首先获取最旧条目的简单方法，我永远不会将日期函数应用于此字段。现在，如果我把它作为日期字段，我会失去两件事：

1. A datetime field is twice the size of an integer
2. Sorting by an integer is faster (not 100% sure of this, pending outcome of this question)

1. 日期时间字段是整数大小的两倍
2. 按整数排序更快（不是 100% 确定这一点，这个问题的待定结果）

However, for another system I had to store transactional information. This made using internal mysql date functionspossible which turned out to be very useful when we had to start doing reports.

但是，对于另一个系统，我必须存储交易信息。这使得使用内部 mysql 日期函数成为可能，这在我们必须开始做报告时非常有用。

回答by cletus

One advantage of using the MySQL date/time typesis to be able to more simply use the date/time functions in MySQL.

使用MySQL 日期/时间类型的优势之一是能够更简单地使用MySQL 中的日期/时间函数。

The DATEtype also has the advantage in that its only storing day, month and year so there is no space wasted or comparison complication that a seconds since epoch time would have for situations where you only cared about the day and not the time.

该DATE类型的另一个优点是它只存储日、月和年，因此没有空间浪费或比较复杂性，因为在您只关心日期而不关心时间的情况下，自纪元时间以来的几秒钟会出现这种情况。

Personally I tend to use a database as just a dump for data so such functions are of little interest. In PHP I tend to just store the date in integer format for pretty much the reasons you state.

我个人倾向于将数据库用作数据的转储，因此对此类功能没什么兴趣。在 PHP 中，我倾向于只以整数格式存储日期，这与您陈述的原因差不多。

回答by hessodreamy

@Smita V, the inefficient query to which you refer is only so because you're applying your conversion function incorrectly to every table row, where you should apply it to the condition itself. So instead of

@Smita V，您引用的低效查询只是因为您将转换函数错误地应用于每个表行，您应该将其应用于条件本身。所以代替

select col1,col2,colUnixdatetime from table where From_Unixtime(colUnixdatetime) between wtvdate1 and wtvdate2

, which converts every row on the table to compare it to the date you've got. You should use

，它会转换表格中的每一行以将其与您获得的日期进行比较。你应该使用

select col1,col2,colUnixdatetime from table where colUnixdatetime between UNIX_TIMESTAMP(wtvdate1) and UNIX_TIMESTAMP(wtvdate2). 

Doing it this way WILL use the appropriate table indexes.

这样做将使用适当的表索引。

@treznik a while ago I moved from a uts integer to a datetime or timestamp data types, for the reasons mentioned above, in that they're much easier to read and manipulate (I do quite a lot of direct table access). However I've lately started to re-think this approach for two reasons:

@treznik 不久前，由于上述原因，我从 uts 整数转移到日期时间或时间戳数据类型，因为它们更易于阅读和操作（我做了很多直接表访问）。但是，由于两个原因，我最近开始重新考虑这种方法：

1. There is no time zone location stored, so you're inferring the time zone based on yourlocation. This may or may not be an issue for you.
2. It ignores daylight saving time. So when the clocks go back at 2am, you will get 1:30am twice, and saying 2011-10-30 01:30 doesn't let you know this, whereas 1319938200 does. I don't think there's a native way in mysql to store date including time zone, except as a string (2011-10-30 01:30 BST).

1. 没有存储时区位置，因此您是根据您的位置推断时区。这对您来说可能是也可能不是问题。
2. 它忽略夏令时。因此，当时钟在凌晨 2 点返回时，您将得到两次凌晨 1:30，并且说 2011-10-30 01:30 不会让您知道这一点，而 1319938200 会。我认为 mysql 中没有本地方式来存储包括时区的日期，除了作为字符串 (2011-10-30 01:30 BST)。

I'm still trying to figure out the answer to this, myself.

我自己仍在努力找出这个问题的答案。

回答by Smita V

Using database datetime is more efficient because every time you need to query you would need to apply from_unixtime() function to extract data from unix datetime col of the table. Using this function in where clause will completely ignore any index usage.

使用数据库日期时间更有效，因为每次需要查询时，您都需要应用 from_unixtime() 函数从表的 unix datetime col 中提取数据。在 where 子句中使用此函数将完全忽略任何索引使用。

say my query is:

说我的查询是：

select col1,col2,colUnixdatetime from table where colUnixdatetime between wtvdate1 and wtvdate2

从表中选择 col1,col2,colUnixdatetime 其中 colUnixdatetime 在 wtvdate1 和 wtvdate2 之间

I would need to run:

我需要运行：

select col1,col2,colUnixdatetime from table where From_Unixtime(colUnixdatetime) between wtvdate1 and wtvdate2

从表中选择 col1,col2,colUnixdatetime 其中 From_Unixtime(colUnixdatetime) 在 wtvdate1 和 wtvdate2 之间

This above query will completely ignore any indexes, well there is no use of indexes here as they will never be used coz I will always have to use a function to get the real date time.

上面的查询将完全忽略任何索引，这里没有使用索引，因为它们永远不会被使用，因为我将始终必须使用一个函数来获取实际日期时间。

Any in-built function used on LHS of condition in a where clause would not use any indexes and if you have a HUGE table, your query will take longer.

在 where 子句中用于 LHS 条件的任何内置函数都不会使用任何索引，如果您有一个巨大的表，您的查询将花费更长的时间。

回答by Todd Gardner

Easier maintenance is a plus. Seeing the actual date when you do:

易于维护是一个优点。在您执行以下操作时查看实际日期：

select * from table where ...

is pretty nice.

很不错。

回答by erenon

Easier to compare, and mysql provides a lot of date functions.

比较容易，mysql提供了很多日期函数。