Time()

Databases
1

Hi,

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.

2

What did you try?

between should work, although you might want to use ‘09:00:00’ >= starttime and ‘09:00:00’ < endtime

3

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

4

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

5

CREATE TABLE individual_availability (
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 (aid)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

6

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)
values
 ('sunday','2010-02-01','2010-02-28','08:00:00','12:00:00')
,('monday','2010-02-01','2010-02-28','08:00:00','08:30:00')
,('friday','2010-02-01','2010-02-28','08:00:00','23:00:00')
;

now let’s try your query – “now i want to check 09:00:00 is shop is open??”

SELECT *
  FROM individual_availability
 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?

7

yes i think it’s right …