When I run the query (please see below) between 08:30:00 and 17:29:59, I
get the data from row 1; if I run the same query between 17:30:00 and
08:29:59, I get the "empty set."
Can anyone explain why?
Many thanks!
Table SQL:
CREATE TABLE `table` (
`id` int(10) unsigned NOT NULL default '0',
`page` varchar(30) NOT NULL default '',
`bgcolor` varchar(7) NOT NULL default '#006633',
`color` varchar(7) NOT NULL default '#ffffff',
`lh_text` text,
`rh_text` text,
`start` time NOT NULL default '00:00:00',
`end` time NOT NULL default '23:59:59',
PRIMARY KEY (`id`,`page`,`start`)
) TYPE=InnoDB PACK_KEYS=1;
Query:
SELECT bgcolor, color, lh_text, rh_text
FROM table
WHERE id = <id_value>
AND page = '<page_value>'
AND CURRENT_TIME() BETWEEN start AND end;
Rows in table:
*************************** 1. row ***************************
id: 1000
page: faqs
bgcolor: #006633
color: #ffffff
lh_text: <right-hand text>
rh_text: <left-hand text>
start: 08:30:00
end: 17:29:59
*************************** 2. row ***************************
id: 1000
page: faqs
bgcolor: #006633
color: #ffffff
lh_text: <right-hand text>
rh_text: <left-hand text>
start: 17:30:00
end: 08:29:59