473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Comparing times with "BETWEEN"

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
2 2013
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Jian H. Li | last post by:
Hello, What's the essential differences between the two ways of "class::member" & "object.member"(or object_pointer->member)? class C{ public: void f() {} int i; };
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
8
by: Lian | last post by:
Hi all, It is a newbie's question about html tag "img". The attributes "title" and "alt" for "img" seems having the same function. So what is the main difference between them? Can i use them at...
6
by: murgan | last post by:
Hi people, i am new to this group,this is my first query, friends i want to know the difference between "function pointer" and "pointer to a function" in c lang, so friends please send the...
3
by: albert.neu | last post by:
Hello! What is the difference between "library parts" of C99 and "language parts" of C99. see...
3
by: Bsr | last post by:
What is the difference between for the following methods. "GET", "HEAD", "PUT" or "POST". Ex:my $req =HTTP::Request->new(GET =>$url1); Bhuvan.
20
by: chutsu | last post by:
I'm trying to compare between pointer and integer in an "IF" statement how do I make this work? if(patient.id != NULL){ } Thanks Chris
4
by: msukumarbabu | last post by:
Hi all, What will be difference between "typedef enum" and "enum". or difference between “typedef structure" and "structure" I am going through some code. in that some place they are using...
3
by: J. Cliff Dyer | last post by:
On Thu, 2008-05-08 at 12:00 -0700, Eric Hanchrow wrote: It's the same reason as this: 5 5 6 5 Python "variables" are just names that point at objects. When you
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.