CODEWITHSUNDEEP
mysql
itskawsar
itskawsar

Reputation: 1242

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.

check 3rd row in data table in below, start time 08:00:00 and end time is 11:00:00. it means 09:00:00 is between 08:00:00 and 11:00:00

Here is my Query:

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 ;

Data in table:

Data in table

Upvotes: 1

Views: 1638

Answers (3)

RandomSeed
RandomSeed

Reputation: 29769

You wrote :

check 3rd row (...) it means 09:00:00 is between 08:00:00 and 11:00:00

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

or alternatively

WHERE ... '09:00:00' BETWEEN start_time AND end_time

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

Upvotes: 2

Mazhar Ahmed
Mazhar Ahmed

Reputation: 1533

You can use between clause of MySQL Have a look here http://www.tutorialspoint.com/mysql/mysql-between-clause.htm

Upvotes: 0

jeffery_the_wind
jeffery_the_wind

Reputation: 18188

From looking at your data, the query should not return any data because there are no suitable rows of data in your table.

Upvotes: -1

Related Questions