MySQL 带有日期和时间的 SQL SELECT WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8496452/
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
SQL SELECT WHERE with date and time
提问by Andrew
I have a SELECTquery where I want to find all rows whose DATE and TIME are between 2011-12-11 23:00:00and 2011-12-12 23:00:00I try to do it with WHERE but row is empty
我有一个SELECT查询，我想找到他们的日期和时间之间的所有行2011-12-11 23:00:00和 2011-12-12 23:00:00我尝试做它用WHERE但排是空的
WHERE (date >= '2011-12-11' AND time > '23:00:00' )
AND (date < '2011-12-12' AND time < '23:00:00' )
Pls, any good suggestion how to change this?
请问，有什么好的建议如何改变这个吗？
回答by JM4
You could use:
你可以使用：
SELECT * FROM table WHERE DATETIME(date) BETWEEN '2011-11-11 23:00:00' AND '2011-12-13 23:00:00'
or separate:
或分开：
SELECT * FROM table WHERE DATETIME(date) > '2011-12-11 23:00:00' AND DATETIME(date) < '2011-12-13 23:00:00'
EDIT:
编辑：
I am not sure I understand what you are trying to achieve here or how your DB is laid out but assuming date and time are separate fields:
我不确定我是否理解您在这里尝试实现的目标或您的数据库的布局方式，但假设日期和时间是单独的字段：
SELECT * FROM table WHERE DATETIME(concat(DATE(date),' ',TIME(time))) BETWEEN '2011-11-11 23:00:00' AND '2011-12-13 23:00:00'
I haven't tested but this may work.
我还没有测试过，但这可能有效。
回答by Andrew
ha, and I have the solution without rebuild the dbase - it's working :))
哈，我有没有重建 dbase 的解决方案 - 它正在工作:))
WHERE
CONCAT(date,' ',time) >= '2011-12-11 23:00:00'
AND
CONCAT(date,' ',time) < '2011-12-12 23:00:00'
Maybe it helps for someone.
也许它对某人有帮助。
thanks for all helping people, brgs
感谢所有帮助过的人，brgs
回答by Brian Hoover
Yup, that's pretty much not going to work. Show me all rows where time is greater than 11 pm and time is less that 11 pm. Time and Date are different fields?
是的，这几乎行不通。显示时间大于晚上 11 点且时间小于晚上 11 点的所有行。时间和日期是不同的字段？
You'll have to be a little more clever building up the query:
您必须更聪明地构建查询：
WHERE (date = '2011-12-11' AND time > '23:00:00' )
or ( date = '2011-12-12' AND time < '23:00:00' )
for a 24 hour window, you just need to have 2 clauses. If you want more than a 24 hour window, you'll need three clauses, one for the start date, one for the end date and one for all the dates in between:
对于 24 小时窗口，您只需要有 2 个子句。如果您想要超过 24 小时的窗口，则需要三个子句，一个用于开始日期，一个用于结束日期，另一个用于两者之间的所有日期：
WHERE (date = '2011-12-11' AND time > '23:00:00' )
or ( date = '2011-12-13' AND time < '23:00:00' )
or (date >='2011-12-12' and date < '2011-12-13')
回答by RolandoMySQLDBA
Let's make sure of certain things
让我们确定一些事情
- You need to get rid of the idea of separate
dateandtimefields when searching - You need to create an additional column in your table called
date_time(type DATETIME) which combines the two fields. - You should probably ditch the separate
dateandtimefields and have justdate_time - You can then create an index on
date_time
- 搜索时需要摆脱分离
date和time字段的想法 - 您需要在表中创建一个名为
date_time（类型 DATETIME）的附加列，它将两个字段组合在一起。 - 您可能应该放弃单独的
date和time字段，而只是date_time - 然后，您可以在其上创建索引
date_time
Here is the command to do that
这是执行此操作的命令
ALTER TABLE yourtable ADD INDEX date_time (date_time);
Once you do these things, THEN you can create a query with a WHERE clause that looks like this:
完成这些操作后，THEN 您可以创建一个带有 WHERE 子句的查询，如下所示：
WHERE date_time >= '2011-12-11 23:00:00'
AND date_time < '2011-12-12 23:00:00'
If you cannot combine the date and time fields, you can still create an index
如果您不能组合日期和时间字段，您仍然可以创建索引
ALTER TABLE yourtable ADD INDEX date_time (date,time);
Given that situation, you can create a query with a WHERE clause that looks like this:
在这种情况下，您可以创建一个带有 WHERE 子句的查询，如下所示：
WHERE (date >= '2011-12-11' AND time >= '23:00:00')
AND (date <= '2011-12-12' AND time < '23:00:00')
The EXPLAIN plan for either situation should result in a fast execution of the query with the use of the date_time index.
任何一种情况的 EXPLAIN 计划都应该使用 date_time 索引快速执行查询。
Give it a Try !!!
试一试 ！！！
回答by Randy
hard to tell without the complete query. also assuming that the date column is actually a date type(?) you would usually do something like TO_DATE('2012-12-11','yyyy-mm-dd')to convert to date types in the comparison.
没有完整的查询很难说。还假设日期列实际上是日期类型（？），您通常会TO_DATE('2012-12-11','yyyy-mm-dd')在比较中执行类似转换为日期类型的操作。
