MySQL 选择日期/时间范围之间的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15821379/
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
Select data between a date/time range
提问by codingknob
How do I select data between a date range in MySQL. My datetimecolumn is in 24-hour zulu time format.
如何在 MySQL 中的日期范围之间选择数据。我的datetime专栏采用 24 小时祖鲁时间格式。
select * from hockey_stats
where game_date between '11/3/2012 00:00:00' and '11/5/2012 23:59:00'
order by game_date desc;
Returns nothing despite having data between these time periods. Do I have to force the values in the 'from'and 'to'fields to datetimetype in the query?
尽管在这些时间段之间有数据，但不返回任何内容。我是否必须强制“from”和“to”字段中的值datetime输入查询？
回答by Eugen Rieck
You need to update the date format:
您需要更新日期格式：
select * from hockey_stats
where game_date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00'
order by game_date desc;
回答by Gordon Linoff
Here is a simple way using the date function:
这是使用日期函数的简单方法：
select *
from hockey_stats
where date(game_date) between date('2012-11-03') and date('2012-11-05')
order by game_date desc
回答by rashedcs
A simple way :
一个简单的方法：
select * from hockey_stats
where game_date >= '2012-03-11' and game_date <= '2012-05-11'
回答by fthiella
You probably need to use STR_TO_DATEfunction:
您可能需要使用STR_TO_DATE函数：
select * from hockey_stats
where
game_date between STR_TO_DATE('11/3/2012 00:00:00', '%c/%e/%Y %H:%i:%s')
and STR_TO_DATE('11/5/2012 23:59:00', '%c/%e/%Y %H:%i:%s')
order by game_date desc;
(if game_date is a string, you might need to use STR_TO_DATE on it)
（如果 game_date 是一个字符串，你可能需要在它上面使用 STR_TO_DATE）
回答by Hafiz Shehbaz Ali
MySQL date format is this : Y-M-D. You are using Y/M/D. That's is wrong. modify your query.
MySQL 日期格式是这样的：YMD。您正在使用 Y/M/D。那是错误的。修改您的查询。
If you insert the date like Y/M/D, It will be insert null value in the database.
如果你插入像 Y/M/D 这样的日期，它会在数据库中插入空值。
If you are using PHP and date you are getting from the form is like this Y/M/D, you can replace this with using the statement .
如果您使用的是 PHP 并且您从表单中获得的日期是这样的 Y/M/D，您可以将其替换为 using 语句。
out_date=date('Y-m-d', strtotime(str_replace('/', '-', $data["input_date"])))
回答by C T Mithun
In a simple way it can be queried as
以一种简单的方式可以查询为
select * from hockey_stats
where game_date between '2018-01-01' and '2018-01-31';
This works if time is not a concern.
如果时间不是问题，这有效。
Considering time also follow in the following way:
考虑时间也遵循以下方式：
select * from hockey_stats where (game_date between '2018-02-05 01:20:00' and '2018-02-05 03:50:00');
Note this is for MySQL server.
请注意，这是针对 MySQL 服务器的。
回答by stackFan
You can either user STR_TO_DATEfunction and pass your own date parameters based on the format you have posted :
您可以使用STR_TO_DATE函数并根据您发布的格式传递您自己的日期参数：
select * from hockey_stats where game_date
between STR_TO_DATE('11/3/2012 00:00:00', '%c/%e/%Y %H:%i:%s')
and STR_TO_DATE('11/5/2012 23:59:00', '%c/%e/%Y %H:%i:%s')
order by game_date desc;
Or just use the format which MySQL handles dates YYYY:MM:DD HH:mm:SS and have the query as
或者只是使用 MySQL 处理日期的格式 YYYY:MM:DD HH:mm:SS 并将查询作为
select * from hockey_stats where game_date between '2012-03-11 00:00:00' and'2012-05-11 23:59:00' order by game_date desc;
select * from hockey_stats where game_date between '2012-03-11 00:00:00' and'2012-05-11 23:59:00' order by game_date desc;
回答by ramber
You must search date defend on how you insert that game_date data on your database.. for example if you inserted date value on long date or short.
您必须搜索日期捍卫如何在数据库中插入游戏日期数据.. 例如，如果您在长日期或短日期插入日期值。
SELECT * FROM hockey_stats WHERE game_date >= "6/11/2018" AND game_date <= "6/17/2018"
You can also use BETWEEN:
您还可以使用BETWEEN：
SELECT * FROM hockey_stats WHERE game_date BETWEEN "6/11/2018" AND "6/17/2018"
simple as that.
就那么简单。
