Comparing times with "BETWEEN"

[Kevin]

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`,`s tart`)
) 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

[Bill Karwin]

Kevin wrote:

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?

See http://dev.mysql.com/doc/mysql/en/Co...Operators.html

In general, the expression:
expr BETWEEN min AND max
is defined as being equivalent to:
min <= expr AND expr <= max

So in your case, you're seeing it fail on:
start <= CURRENT_TIME() AND CURRENT_TIME() <= end
which cannot be true if start > end.

Try this:
CURRENT_TIME() BETWEEN LEAST(start, end) AND GREATEST(start, end)

Bill K.

[Leif]

"Kevin" <lw************ ***@mynospammer sway.com> skrev i en meddelelse
news:pa******** *************** *****@mynospamm ersway.com...

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?

One could say "The comparison is linear, not circular."

It can't be both after 17:30 and before 8:30, because 1:00 is not greater
than 23:00

Instead say "not (between 08:30:00 and 17:29:59)"

Leif