How do I select data between a date range in MySQL. My datetime
column 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 datetime
type in the query?
尽管在这些时间段之间有数据,但没有返回任何内容。是否必须强制查询中的“from”和“to”字段中的值转换为datetime类型?
8 个解决方案
#1
83
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;
#2
13
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
#3
5
A simple way :
一个简单的方法:
select * from hockey_stats
where game_date >= '2012-03-11' and game_date <= '2012-05-11'
#4
4
You probably need to use STR_TO_DATE function:
您可能需要使用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)
#5
3
MySQL date format is this : Y-M-D. You are using Y/M/D. That's is wrong. modify your query.
MySQL日期格式是:Y-M-D。您使用的是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,您可以使用语句替换它。
out_date=date('Y-m-d', strtotime(str_replace('/', '-', $data["input_date"])))
#6
1
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.
考虑时间也遵循以下方式:从hockey_stats中选择*(在‘2018-02-05 01:20:00’和‘2018-02-05 03:50:00’之间的game_date);注意这是MySQL服务器。
#7
0
You can either user STR_TO_DATE function 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:MM: SS,并将查询设为as
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;
在“2012-03-11 00:00”和“2012-05-11 23:59:00”之间,通过game_date desc从hockey_stats中选择*;
#8
0
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.
您必须根据如何在数据库中插入game_date数据来搜索date protect。例如,如果您在长日期或短时间内插入日期值。
SELECT * FROM hockey_stats WHERE game_date >= "6/11/2018" AND game_date <= "6/17/2018"
You can also use BETWEEN:
你也可以使用之间:
SELECT * FROM hockey_stats WHERE game_date BETWEEN "6/11/2018" AND "6/17/2018"
simple as that.
就这么简单。
#1
83
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;
#2
13
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
#3
5
A simple way :
一个简单的方法:
select * from hockey_stats
where game_date >= '2012-03-11' and game_date <= '2012-05-11'
#4
4
You probably need to use STR_TO_DATE function:
您可能需要使用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)
#5
3
MySQL date format is this : Y-M-D. You are using Y/M/D. That's is wrong. modify your query.
MySQL日期格式是:Y-M-D。您使用的是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,您可以使用语句替换它。
out_date=date('Y-m-d', strtotime(str_replace('/', '-', $data["input_date"])))
#6
1
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.
考虑时间也遵循以下方式:从hockey_stats中选择*(在‘2018-02-05 01:20:00’和‘2018-02-05 03:50:00’之间的game_date);注意这是MySQL服务器。
#7
0
You can either user STR_TO_DATE function 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:MM: SS,并将查询设为as
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;
在“2012-03-11 00:00”和“2012-05-11 23:59:00”之间,通过game_date desc从hockey_stats中选择*;
#8
0
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.
您必须根据如何在数据库中插入game_date数据来搜索date protect。例如,如果您在长日期或短时间内插入日期值。
SELECT * FROM hockey_stats WHERE game_date >= "6/11/2018" AND game_date <= "6/17/2018"
You can also use BETWEEN:
你也可以使用之间:
SELECT * FROM hockey_stats WHERE game_date BETWEEN "6/11/2018" AND "6/17/2018"
simple as that.
就这么简单。