在日期/时间范围之间选择数据

如何在 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;

尽管在这些时间段之间有数据，但不返回任何值。是否必须强制 “来自”和 “到”字段中的值在查询中输入 datetime？

308986 次浏览
小开
最佳答案

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;
小开

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
小开

You probably need to use STR_TO_DATE function:

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)

小开

MySQL date format is this : Y-M-D. You are using Y/M/D. That's is wrong. modify your query.

If you insert the date like Y/M/D, It will be insert null value in the database.

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 . 

out_date=date('Y-m-d', strtotime(str_replace('/', '-', $data["input_date"])))
小开

A simple way : 

select  * from  hockey_stats
where  game_date >= '2012-03-11' and game_date  <= '2012-05-11'
小开

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.

小开

You can either user STR_TO_DATE function and pass your own date parameters based on the format you have posted :

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

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;

小开

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:

SELECT * FROM hockey_stats WHERE game_date BETWEEN "6/11/2018" AND "6/17/2018"

simple as that.


提交答案