MySQL:无法选择具有时间范围的数据 [英] MySQL: could not select data with time range
问题描述
如果日期匹配并且时间在开始时间和结束时间之间,我想选择数据.我已经尝试了很多方法,但是我没有成功.
I wanted to select data, if date is matched and time is in between start time and end time. I have tried in many ways, but i am not succeed.
在下面的数据表中检查第三行,开始时间为08:00:00,结束时间为11:00:00.这表示09:00:00在08:00:00到11:00:00之间
这是我的查询:
SELECT * FROM (`rides`) WHERE `date` = '2013/04/30' AND `start_time` >= '9:00:00' AND `end_time` <= '9:00:00'
表结构,如果您需要了解字段的数据类型:
Table structure if you need to know the datatype of field:
CREATE TABLE IF NOT EXISTS `rides` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...,
...,
`date` date NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
表中的数据:
推荐答案
您写道:
检查第三行(...),表示09:00:00在08:00:00到11:00:00之间
check 3rd row (...) it means 09:00:00 is between 08:00:00 and 11:00:00
我假设您期望结果集中的这一行.显然,您的WHERE
条件是错误的.您想要的是:
I am assuming you expect this row in your result set. Obviously, your WHERE
condition is wrong. What you want is:
WHERE ... start_time <= '9:00:00' AND '9:00:00' <= end_time
或者
WHERE ... '09:00:00' BETWEEN start_time AND end_time
请注意,在上面的示例中,前导0
是必需的.更为规范的形式是:
Notice the leading 0
is required in the above example. A more canonical form would be:
WHERE ... CAST('9:00:00' AS TIME) BETWEEN start_time AND end_time
这篇关于MySQL:无法选择具有时间范围的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!