I have following record
day starttime endtime
sun 08:00:00 12:00:00
now i want to check 09:00:00 is shop is open??
so how am i suppose to do that i tried it with between and like dates but it doesn't work as expected
for 09:00:00 it sud show me the above record....it is not!!
i know this is simple but i am very confuse as of now.
What did you try?
between should work, although you might want to use '09:00:00' >= starttime and '09:00:00' < endtime
Is following query correct with the view of performance!! is it a good practice !!
SELECT * FROM table WHERE day='Friday' AND '2010-01-29' BETWEEN start_date and end_date
could you do a SHOW CREATE TABLE please
i want to see this column called "day" as well as how your starttime and endtime columns are defined
'2010-01-29' BETWEEN start_date and end_date is okay, except you don't have columns like that, do you?
another reason to display your SHOW CREATE TABLE
aid bigint(20) unsigned NOT NULL AUTO_INCREMENT,
day enum('sunday','monday','tuesday','wednesday','thursday','friday','saturday') NOT NULL,
start_date date DEFAULT NULL,
end_date date DEFAULT NULL,
start_time time DEFAULT NULL,
end_time time DEFAULT NULL,
PRIMARY KEY (
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1
okay, let's use the data you provided in post #1 and a couple of additional rows...
insert into individual_availability
( day, start_date, end_date, start_time, end_time)
now let's try your query -- "now i want to check 09:00:00 is shop is open??"
WHERE '09:00:00' BETWEEN start_time AND end_time
aid day start_date end_date start_time end_time
32 sunday 2010-02-01 2010-02-28 08:00:00 12:00:00
34 friday 2010-02-01 2010-02-28 08:00:00 23:00:00
i think this is correct, what do you think?
yes i think it's right ..