Mysql select current time between two columns

user_4572726 0
Viewed 116

I have a schedule table in which I have two columns time_from and time_to with values in the 24 hour format like if the from and to are 9 Pm to 1am than the time_from and time_to would be 21 and 1 respectively.

Now I need to check if the current time falls between these two columns

If the time is 11 Pm now I should get the column whose in which column value in between 11 Pm that is 23 and 1

CREATE TABLE `ace_rp_inventory_locations_night` (
 `id` int(11)  NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `time_from` varchar(255) DEFAULT NULL,
 `time_to` varchar(255) DEFAULT NULL
 ); 
 
INSERT INTO ace_rp_inventory_locations_night (id, name, time_from, time_to)
VALUES (1, 'test', '23','1');

please check sql fiddle

this query works

SELECT *
FROM `ace_rp_inventory_locations_night`
WHERE time_from_1 <= '23'
AND time_to_1 >= '23'

if the time is in 24 Hours range that is 20 and 24 but the value for 1am is 1 and the above query does not work because 23 and 1 does not full fill the condition I am checking it with the current time so if the time is 2am I have to pass 2 in the query.

How can I use the current time and use it to query between two columns?

between time mysql sql
1 Answers

You can use:

SELECT *
FROM `ace_rp_inventory_locations_night`
WHERE (time_from_1 <= time_to_1 AND 23 between time_from_1 and time_to_1) OR
      (time_to_1 > time_to_1 AND 23 NOT BETWEEN time_from_1 and time_to_1);

Note: This uses BETWEEN which affect how the end points of the ranges are handled. Your question doesn't specify what to do. You should probably be explicit about the inequalities.

user_11440350
Related