By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,212 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,212 IT Pros & Developers. It's quick & easy.

Comparing times with "BETWEEN"

P: n/a
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

Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Jul 20 '05 #2

P: n/a

"Kevin" <lw***************@mynospammersway.com> skrev i en meddelelse
news:pa****************************@mynospammerswa y.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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.