日期时间与日期和时间 Mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18356268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Datetime vs Date and Time Mysql
提问by abhinsit
I generally use datetime field to store created_time updated time of data within an application.
我通常使用 datetime 字段来存储应用程序中数据的 created_time 更新时间。
But now i have come across a database table where they have kept date and time separate fields in table.
但是现在我遇到了一个数据库表，他们在表中保留了日期和时间单独的字段。
- So what are the schema in which two of these should be used and why?
- What are pros and cons attached with using of two?
- 那么应该使用其中两个的模式是什么，为什么？
- 使用两个有什么优点和缺点？
回答by Elwin
There is a huge difference in performance when using DATE field above DATETIME field. I have a table with more then 4.000.000 records and for testing purposes I added 2 fields with both their own index. One using DATETIME and the other field using DATE.
在 DATETIME 字段之上使用 DATE 字段时，性能会有巨大差异。我有一个包含超过 4.000.000 条记录的表，出于测试目的，我添加了 2 个带有自己索引的字段。一个使用 DATETIME，另一个字段使用 DATE。
I disabled MySQL query cache to be able to test properly and looped over the same query for 1000x:
我禁用了 MySQL 查询缓存以便能够正确测试并循环 1000 倍的相同查询：
SELECT * FROM `logs` WHERE `dt` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10;DATETIME INDEX:
197.564 seconds.
SELECT * FROM `logs` WHERE `dt` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10;日期时间指数：197.564 秒。
SELECT * FROM `logs` WHERE `d` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10;DATE INDEX:
107.577 seconds.
SELECT * FROM `logs` WHERE `d` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10;日期索引：107.577 秒。
Using a date indexed field has a performance improvement of: 45.55%!!
使用日期索引字段的性能提升为：45.55%！！
So I would say if you are expecting a lot of data in your table please consider in separating the date from the time with their own index.
所以我想说，如果您希望表中有大量数据，请考虑使用自己的索引将日期与时间分开。
回答by Gordon Linoff
I tend to think there are basically no advantages to storing the date and time in separate fields. MySQL offers very convenient functions for extracting the date and time parts of a datetimevalue.
我倾向于认为将日期和时间存储在不同的字段中基本上没有优势。MySQL 提供了非常方便的函数来提取值的日期和时间部分datetime。
Okay. There can be some efficiency reasons. In MySQL, you can put separate indexes on the fields. So, if you want to search for particular times, for instance, then a query that counts by hours of the day (for instance) can use an index on the timefield. An index on a datetimefield would not be used in this case. A separate datefield might make it easier to write a query that will use the dateindex, but, strictly speaking, a datetimeshould also work.
好的。可能有一些效率原因。在 MySQL 中，您可以在字段上放置单独的索引。因此，例如，如果您想搜索特定时间，那么按一天中的小时计数的查询（例如）可以在该time字段上使用索引。datetime在这种情况下不会使用字段上的索引。单独的date字段可能会使编写将使用date索引的查询更容易，但严格来说， adatetime也应该有效。
The one time where I've seen dates and times stored separately is in a trading system. In this case, the trade has a valuation date. The valuation time is something like "NY Open" or "London Close" -- this is not a real time value. It is a description of the time of day used for valuation.
有一次我看到单独存储的日期和时间是在交易系统中。在这种情况下，交易有一个估值日期。估值时间类似于“纽约开盘价”或“伦敦收盘价”——这不是实时值。它是对用于估值的一天中的时间的描述。
回答by victor diaz
The tricky part is when you have to do date arithmetic on a time value and you do not want a date portion coming into the mix. Ex:
棘手的部分是当您必须对时间值进行日期算术并且您不希望日期部分混入时。前任：
myapptdate = 2014-01-02 09:00:00
myapptdate = 2014-01-02 09:00:00
Select such and such where myapptdate between 2014-01-02 07:00:00 and 2014-01-02 13:00:00
在 2014-01-02 07:00:00 和 2014-01-02 13:00:00 之间选择 myapptdate
1900-01-02 07:00:00 2014-01-02 07:00:00
1900-01-02 07:00:00 2014-01-02 07:00:00
