MySQL - 从两个日期之间的数据库中选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8458290/
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
MySQL - select data from database between two dates
提问by user984621
I have saved the dates of a user's registration as a datetime, so that's for instance 2011-12-06 10:45:36. I have run this query and I expected this item - 2011-12-06 10:45:36- will be selected:
我已将用户注册的日期保存为日期时间，例如2011-12-06 10:45:36。我已经运行了这个查询，我希望这个项目 - 2011-12-06 10:45:36- 将被选中：
SELECT `users`.* FROM `users` WHERE created_at >= '2011-12-01' AND
created_at <= '2011-12-06'
But is not. Exist any elegant way, how to select this item? As a first idea that I got was like 2011-12-06 + 1, but this doesn't looks very nice.
但不是。存在任何优雅的方式，如何选择此项？我的第一个想法是2011-12-06 + 1，但这看起来不太好。
回答by dash
Your problem is that the short version of dates uses midnight as the default. So your query is actually:
您的问题是日期的短版本使用午夜作为默认值。所以你的查询实际上是：
SELECT users.* FROM users
WHERE created_at >= '2011-12-01 00:00:00'
AND created_at <= '2011-12-06 00:00:00'
This is why you aren't seeing the record for 10:45.
这就是为什么您没有看到 10:45 的记录。
Change it to:
将其更改为：
SELECT users.* FROM users
WHERE created_at >= '2011-12-01'
AND created_at <= '2011-12-07'
You can also use:
您还可以使用：
SELECT users.* from users
WHERE created_at >= '2011-12-01'
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)
Which will select all users in the same interval you are looking for.
这将选择您正在寻找的同一时间间隔内的所有用户。
You might also find the BETWEEN operator more readable:
您可能还会发现 BETWEEN 运算符更具可读性：
SELECT users.* from users
WHERE created_at BETWEEN('2011-12-01', date_add('2011-12-01', INTERVAL 7 DAY));
回答by Martin York
You need to use '2011-12-07' as the end point as a date without a time default to time 00:00:00.
您需要使用 '2011-12-07' 作为终点作为没有时间默认为时间 00:00:00 的日期。
So what you have actually written is interpreted as:
所以你实际写的内容被解释为：
SELECT users.*
FROM users
WHERE created_at >= '2011-12-01 00:00:00'
AND created_at <= '2011-12-06 00:00:00'
And your time stamp is: 2011-12-06 10:45:36 which is not between those points.
Change this too:
而您的时间戳是： 2011-12-06 10:45:36 不在这些点之间。
也改一下：
SELECT users.*
FROM users
WHERE created_at >= '2011-12-01' -- Implied 00:00:00
AND created_at < '2011-12-07' -- Implied 00:00:00 and smaller than
-- thus any time on 06
回答by Marek P?íhoda
SELECT users.* FROM users WHERE created_at BETWEEN '2011-12-01' AND '2011-12-07';
回答by user7618441
Another alternative is to use DATE()function on the left hand operand as shown below
另一种选择是DATE()在左侧操作数上使用函数，如下所示
SELECT users.* FROM users WHERE DATE(created_at) BETWEEN '2011-12-01' AND '2011-12-06'
回答by vextorspace
Have you tried before and after rather than >= and <=? Also, is this a date or a timestamp?
您是否尝试过之前和之后而不是 >= 和 <=？另外，这是日期还是时间戳？
回答by Brian Hoover
Searching for created_at <= '2011-12-06'will search for any records that where created at or before midnight on 2011-12-06
. You want to search for created_at < '2011-12-07'.
搜索created_at <= '2011-12-06'将搜索在 2011-12-06 午夜或之前创建的任何记录。您要搜索created_at < '2011-12-07'.
回答by sys_debug
Maybe use in between better. It worked for me to get range then filter it
也许在两者之间使用更好。获取范围然后过滤它对我有用
回答by Koushik Das
You can use MySQL DATEfunction like below
您可以使用DATE如下所示的MySQL函数
For instance, if you want results between 2017-09-05 till 2017-09-09
例如，如果您想要 2017-09-05 到 2017-09-09 之间的结果
SELECT DATE(timestamp_field) as date FROM stocks_annc WHERE DATE(timestamp_field) >= '2017-09-05' AND DATE(timestamp_field) <= '2017-09-09'
Make sure to wrap the dates within single quotation ''
确保将日期用单引号括起来 ''
Hope this helps.
希望这可以帮助。
