如何使用 MySQL 在两个日期之间进行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3822648/
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
How do I query between two dates using MySQL?
提问by NullVoxPopuli
The following query:
以下查询：
SELECT * FROM `objects`
WHERE (date_field BETWEEN '2010-09-29 10:15:55' AND '2010-01-30 14:15:55')
returns nothing.
什么都不返回。
I should have more than enough data to for the query to work though. What am I doing wrong?
我应该有足够的数据来让查询工作。我究竟做错了什么？
回答by Daniel Vandersluis
Your second date is before your first date (ie. you are querying between September 29 2010 and January 30 2010). Try reversing the order of the dates:
您的第二个日期在您的第一个日期之前（即您在 2010 年 9 月 29 日和 2010 年 1 月 30 日之间进行查询）。尝试颠倒日期顺序：
SELECT *
FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
回答by Nik
Your query should have date as
您的查询日期应为
select * from table between `lowerdate` and `upperdate`
try
尝试
SELECT * FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
回答by Rocket Hazmat
Is date_fieldof type datetime? Also you need to put the eariler date first.
是date_field类型datetime？此外，您还需要先输入早期日期。
It should be:
它应该是：
SELECT * FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
回答by sabin
DATE() is a MySQL function that extracts only the date part of a date or date/time expression
DATE() 是一个 MySQL 函数，它只提取日期或日期/时间表达式的日期部分
SELECT * FROM table_name WHERE DATE(date_field) BETWEEN '2016-12-01' AND '2016-12-10';
回答by ltlBeBoy
As extension to the answer from @sabin and a hint if one wants to compare the datepart only (without the time):
作为@sabin 答案的扩展以及如果只想比较日期部分（没有时间）的提示：
If the field to compare is from type datetimeand only datesare specified for comparison, then these datesare internally converted to datetimevalues. This means that the following query
如果要比较的字段来自datetime类型，并且只指定了日期进行比较，则这些日期会在内部转换为日期时间值。这意味着以下查询
SELECT * FROM `objects` WHERE (date_time_field BETWEEN '2010-01-30' AND '2010-09-29')
will be converted to
将被转换为
SELECT * FROM `objects` WHERE (date_time_field BETWEEN '2010-01-30 00:00:00' AND '2010-09-29 00:00:00')
internally.
内部。
This in turn leads to a result that does not include the objects from 2010-09-29 with a time value greater than 00:00:00!
这反过来导致结果不包括 2010-09-29 时间值大于 00:00:00 的对象！
Thus, if all objects with date 2010-09-29 should be included too, the field to compare has to be converted to a date:
因此，如果还应包括日期为 2010-09-29 的所有对象，则必须将要比较的字段转换为日期：
SELECT * FROM `objects` WHERE (DATE(date_time_field) BETWEEN '2010-01-30' AND '2010-09-29')
回答by Fahd Allebdi
You can do it manually, by comparing with greater than or equal and less than or equal.
您可以通过与大于或等于和小于或等于进行比较来手动执行此操作。
select * from table_name where created_at_column >= lower_date and created_at_column <= upper_date;
In our example, we need to retrieve data from a particular day to day. We will compare from the beginning of the day to the latest second in another day.
在我们的示例中，我们需要每天检索特定日期的数据。我们将从一天的开始与另一天的最新一秒进行比较。
select * from table_name where created_at_column >= '2018-09-01 00:00:00' and created_at_column <= '2018-09-05 23:59:59';
回答by DS Mercy
Just Cast date_field as date
只需将 date_field 投射为日期
SELECT * FROM `objects`
WHERE (cast(date_field as date) BETWEEN '2010-09-29' AND
'2010-01-30' )
回答by Bruno Barral
When using Date andTime values, you must cast the fields as DateTimeand not Date.
Try :
使用日期和时间值时，您必须将字段转换为DateTime而不是Date。尝试 ：
SELECT * FROM `objects`
WHERE (CAST(date_field AS DATETIME)
BETWEEN CAST('2010-09-29 10:15:55' AS DATETIME) AND CAST('2010-01-30 14:15:55' AS DATETIME))
回答by SoulWanderer
Might be a problem with date configuration on server side or on client side. I've found this to be a common problem on multiple databases when the host is configured in spanish, french or whatever... that could affect the format dd/mm/yyyy or mm/dd/yyyy.
可能是服务器端或客户端的日期配置问题。当主机以西班牙语、法语或其他语言配置时，我发现这是多个数据库上的常见问题......这可能会影响格式 dd/mm/yyyy 或 mm/dd/yyyy。
回答by Jure1873
Try switching the dates around:
尝试切换日期：
2010-09-29 > 2010-01-30?
