CODEWITHSUNDEEP
mysqltime
Bilal Rabbi
Bilal Rabbi

Reputation: 1840

mysql query to find that time is between from and to time

I have a from_time an to_time in my table. one of the record is from_time: 19:00:00 and to_time: 01:30:00 if user select 12:30 AM or 11:00 PM. how do i correctly find that it is between from_time and to_time.

Mysql query i tried: 

SELECT * FROM `table` WHERE  '19:00:00' >= from_time  and '19:00:00' <= to_time

is not working.

Upvotes: 0

Views: 119

Answers (3)

P.Salmon
P.Salmon

Reputation: 17615

For to_times which go over midnight you could add 24hours and then test. For example

drop table if exists t;
create table t(from_time time, to_time time);
insert into t values
('18:00:00','20:00:00'),
('18:00:00','01:00:00'),
('16:00:00','18:00:00');



select from_time,to_time,
         case when  '19:00:00' between 
                from_time and
         if(to_time < from_time, 
         addtime(to_time,'24:00:00'),
          to_time) 
          then 'true'
         else 'false'
         end as truefalse
from t;
+-----------+----------+-----------+
| from_time | to_time  | truefalse |
+-----------+----------+-----------+
| 18:00:00  | 20:00:00 | true      |
| 18:00:00  | 01:00:00 | true      |
| 16:00:00  | 18:00:00 | false     |
+-----------+----------+-----------+
3 rows in set (0.00 sec)

Upvotes: 0

Clinton
Clinton

Reputation: 1196

Your question does not show where the selected time is coming from. Is it in a column, is it something that the user sets in the SQL or is it sent through code?

If the time is set in the SQL then the way to do this is as follows:

SET @mytime := '19:00:00';
SELECT * FROM `table` WHERE CAST(@mytime AS TIME) BETWEEN from_time AND to_time;

where @mytime is the user set variable and assuming that from_time and to_time are time format.

My test table for the above was as follows:

CREATE TABLE `timetest` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_time` time DEFAULT NULL,
  `to_time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

with test data as:

INSERT INTO `timetest` (`id`, `from_time`, `to_time`) VALUES
(1, '09:00:00', '11:00:00'),
(2, '06:30:00', '14:00:00'),
(3, '14:29:10', '16:12:18'),
(4, '09:11:17', '23:00:01'),
(5, '18:00:00', '23:59:59'),
(6, '01:12:00', '11:59:44');

Upvotes: 0

Devsi Odedra
Devsi Odedra

Reputation: 5322

You used value instead of column name

You can use as below query. You can change value as per your need.

Change Time value as per your need first.

First Option with Where

SELECT * FROM `table` WHERE  from_time >= '00:00:00'  and to_time <= '23:59:59'

Second option is between

SELECT * FROM `table` WHERE  from_time between '00:00:00'  and '23:59:59'

Upvotes: 1

Related Questions