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屋！